查询存储功能提供有关 SQL Server、Azure SQL 数据库、Azure SQL 托管实例和 Azure Synapse Analytics 的查询计划选择和性能的见解。 查询存储可帮助你快速找到查询计划更改所造成的性能差异，从而简化性能疑难解答。 查询存储将自动捕获查询、计划和运行时统计信息的历史记录，并保留它们以供查阅。 它按时间窗口将数据分割开来，使你可以查看数据库使用模式并了解服务器上何时发生了查询计划更改。 可以使用 ALTER DATABASE SET 选项来配置查询存储。
由于统计信息更改、架构更改、索引的创建/删除等多种不同原因，SQL Server 中任何特定查询的执行计划通常会随着时间而改进。过程缓存（其中存储了缓存的查询计划）仅存储最近的执行计划。 还会由于内存压力从计划缓存中逐出计划。 因此，执行计划更改造成的查询性能回归可能非常重大，且长时间才能解决。
由于查询存储会保留每个查询的多个执行计划，因此它可以强制执行策略，以引导查询处理器对某个查询使用特定执行计划。 这称为“计划强制”。 查询存储中的计划强制是通过使用类似于 USE PLAN 查询提示的机制来提供的，但它不需要在用户应用程序中进行任何更改。 计划强制可在非常短的时间内解决由计划更改造成的查询性能回归。
根据设计，查询存储不会收集 CREATE INDEX 等 DDL 语句的计划。查询存储通过收集基础 DML 语句的计划来捕获累积资源消耗。 例如，查询存储可能会显示在内部执行的 SELECT 和 INSERT 语句以填充新索引。
次要副本的查询存储功能在次要副本工作负载上启用可用于主要副本的相同的查询存储功能。 启用次要副本的查询存储后，副本会将通常存储在查询存储中的查询执行信息发送回主要副本。 然后，主要副本会将数据保存到自身查询存储中的磁盘。 从本质上讲，主要副本和所有次要副本之间共享有一个查询存储。 查询存储存在于主要副本上，将所有副本的数据一起存储。
选择某个计划以查看图形查询计划。 可以使用按钮查看源查询、强制执行和取消强制执行查询计划、在网格和图表格式之间进行切换、比较所选的计划（如果选择多个）及刷新显示。
使用顶部的下拉列表框，根据各种等待时间条件为所选等待类别筛选查询：平均值、最大值、最小值、标准偏差和总计（默认）。 选择某个计划以查看图形查询计划。 可使用按钮来查看源查询，强制执行和取消强制执行某一查询计划，以及刷新显示内容。
等待类别可将不同等待类型按性质合并为类似的桶。 不同的等待类别需要不同的后续分析才能解决此问题，但相同类别的等待类型可引起非常相似的故障排除体验，并假定基于等待的受影响的查询会成为用于成功完成大部分此类调查所缺少的部分。
1 在极端情况下，查询存储可能由于内部错误而进入 ERROR 状态。 从 SQL Server 2017 (14.x) 开始，如果出现这种情况，可通过在受影响的数据库内执行 sp_query_store_consistency_check 存储过程来恢复查询存储。 请参阅 sys.database_query_store_options，了解 actual_state_desc 列说明中所述的详细信息。
自 SQL Server Management Studio v18 起，用户可借助查询优化助手按指导操作建议的工作流。 有关详细信息，请参阅[使用查询优化助手升级
可以毫无顾忌地在所有数据库中使用 Query Store，甚至是在密集打包的 Azure SQL 数据库弹性池中。 之前与资源过度使用（为弹性池中的大量数据库启用了查询存储时可能会遇到这种情况）相关的所有问题都已得到了解决。
查询存储收集能够准确代表工作负载的数据集需要一定的时间。 通常情况下，即使是很复杂的工作负荷，一天的时间也足够了。 但是，在启用此功能后，就可以立即开始浏览数据并确定需要注意的查询。 转到 Management Studio 的对象资源管理器中数据库节点下的查询存储子文件夹，然后打开特定方案的故障排除视图。
查询存储可在无提示的情况下更改操作模式。 请定期监视查询存储的状态以确保查询存储正常运行，并采取相应措施，避免发生不必要的故障。 执行以下查询，以便确定操作模式并查看最相关的参数：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE); GO SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb, readonly_reason, interval_length_minutes, stale_query_threshold_days, size_based_cleanup_mode_desc, query_capture_mode_desc FROM sys.database_query_store_options; |
如果问题仍然存在，则表明磁盘上的查询存储数据已永久损坏。
从 SQL Server 2017 (14.x) 开始，可通过在受影响的数据库内执行 sys.sp_query_store_consistency_check 存储过程来恢复查询存储。 必须先禁用查询存储，然后才能尝试恢复操作。 可使用或修改以下示例查询，完成 QDS 的一致性检查和恢复：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3) BEGIN BEGIN TRY ALTER DATABASE [QDS] SET QUERY_STORE = OFF Exec [QDS].dbo.sp_query_store_consistency_check ALTER DATABASE [QDS] SET QUERY_STORE = ON ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE) END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH; END |
对于 SQL Server 2016 (13.x)，需要从查询存储中清除数据，如下所示。
如果恢复失败，可先尝试清除查询存储，然后再设置读写模式。
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR; GO ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE); GO SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb, readonly_reason, interval_length_minutes, stale_query_threshold_days, size_based_cleanup_mode_desc, query_capture_mode_desc FROM sys.database_query_store_options; |
避免使用非参数化查询
在不必要的情况下使用非参数化查询不是最佳做法。 临时分析就是一个示例。 不能重复使用缓存的计划，因为这会强制查询优化器在碰到每个唯一的查询文本时都进行查询编译。 有关详细信息，请参阅强制参数化使用指南。
此外，查询存储可能会很快超过大小配额，因为可能会存在大量不同的查询文本，导致存在大量不同但具有相同形状的执行计划。 结果就是，工作负载性能无法优化，查询存储可能会切换为只读模式，或者可能会不断删除数据以应对传入的查询。
请考虑以下选项：
在适用时进行参数化查询。 例如，在存储过程或 sp_executesql 中包装查询。 有关详细信息，请参阅参数和执行计划重用。
如果工作负载包含许多一次性使用的临时批处理且查询计划各不相同，请使用
针对临时工作负载进行优化
选项。
- 将不同 query_hash 值的数目与
sys.query_store_query 中项的总数进行比较。 如果该比率接近 1，则说明临时工作负载生成了不同的查询。
如果不同查询计划的数量不多，请对数据库或部分查询应用
强制参数化
。
将 QUERY_CAPTURE_MODE 设置为 AUTO 即可自动筛选掉资源消耗小的即席查询。
提示
使用对象关系映射 (ORM) 解决方案（如实体框架 (EF)）时，手动 LINQ 查询树或某些原始 SQL 查询等应用程序查询可能不会参数化，这会影响计划重新使用以及在查询存储中跟踪查询的能力。 有关详细信息，请参阅 EF 查询缓存和参数化以及 EF 原始 SQL 查询。
在查询存储中查找非参数化查询
可以使用以下查询，使用查询存储 DMV，在 SQL Server、Azure SQL 托管实例或 Azure SQL 数据库中查找存储在查询存储中的计划数量：
SQL
| SELECT count(Pl.plan_id) AS plan_count, Qry.query_hash, Txt.query_text_id, Txt.query_sql_text FROM sys.query_store_plan AS Pl INNER JOIN sys.query_store_query AS Qry ON Pl.query_id = Qry.query_id INNER JOIN sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text ORDER BY plan_count desc; |
以下示例创建一个扩展事件会话来捕获事件 query_store_db_diagnostics，这在诊断查询资源消耗方面很有用。 在 SQL Server 中，此扩展事件会话会默认在 SQL Server 日志文件夹中创建一个事件文件。 例如，在 Windows 上默认安装 SQL Server 2019 (15.x) 时，会在文件夹 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log 中创建事件文件（.xel 文件）。 对于 Azure SQL 托管实例，请改为指定 Azure Blob 存储位置。 有关详细信息，请参阅 Azure SQL 托管实例的 XEvent event_file。 事件“qds.query_store_db_diagnostics”不适用于 Azure SQL 数据库。
SQL
| CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER ADD EVENT qds.query_store_db_diagnostics( ACTION(sqlos.system_thread_id,sqlos.task_address,sqlos.task_time,sqlserver.database_id,sqlserver.database_name)) ADD TARGET package0.event_file(SET filename=N'QueryStore',max_file_size=(100)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF); |
使用这些数据，可以查找查询存储中的计划计数，以及许多其他统计信息。 在事件数据中查找 plan_count、query_count、max_stmt_hash_map_size_kb 和 max_size_mb 列，了解使用的内存量和查询存储跟踪的计划数量。 如果计划计数高于正常值，则表示非参数化查询有所增加。 使用以下查询存储 DMV 查询查看查询存储中的参数化查询和非参数化查询。
对于参数化查询：
SQL
| SELECT qsq.query_id, qsqt.query_sql_text FROM sys.query_store_query AS qsq INNER JOIN sys.query_store_query_text AS qsqt ON qsq.query_text_id= qsqt.query_text_id WHERE qsq.query_parameterization_type<>0 or qsqt.query_sql_text like '%@%'; |
对于非参数化查询：
SQL
| SELECT qsq.query_id, qsqt.query_sql_text FROM sys.query_store_query AS qsq INNER JOIN sys.query_store_query_text AS qsqt ON qsq.query_text_id= qsqt.query_text_id WHERE query_parameterization_type=0; |
避免对包含对象使用 DROP 和 CREATE 模式
查询存储会将查询条目与包含对象（例如存储过程、函数和触发器）相关联。 重新创建包含对象时，会针对同一查询文本生成新的查询条目。 这会阻止你跟踪该查询在一定时段内的性能统计信息，并会使用计划强制机制。 若要避免这种情况，请尽可能使用 ALTER <object> 过程来更改包含对象定义。
定期检查强制计划的状态
可以方便地使用计划强制机制来修复关键查询的性能问题，使这些查询的结果更可预测。 与计划提示和计划指南一样，强制实施某项计划并不能确保在今后的执行过程中会用到它。 通常情况下，如果对数据库架构的更改导致执行计划所引用的对象被更改或删除，计划强制就会失败。 在这种情况下，SQL Server 会回退到重新编译查询，而强制失败的实际原因则显示在 sys.query_store_plan 中。 以下查询返回强制计划的相关信息：
SQL
| USE [QueryStoreDB]; GO SELECT p.plan_id, p.query_id, q.object_id as containing_object_id, force_failure_count, last_force_failure_reason_desc FROM sys.query_store_plan AS p JOIN sys.query_store_query AS q on p.query_id = q.query_id WHERE is_forced_plan = 1; |
有关原因的完整列表，请参阅 sys.query_store_plan。 你还可以使用 query_store_plan_forcing_failed XEvent 来跟踪和故障排除计划强制失败情况。
提示
在 Azure SQL 数据库中，考虑使用查询存储提示功能，该功能可以在不更改代码的情况下对查询强制执行查询提示。 有关详细信息和示例，请参阅查询存储提示。
避免在使用强制计划执行查询时重命名数据库
执行计划使用由三个部分组成的名称（例如 database.schema.object）来引用对象。
如果重命名数据库，计划强制就会失败，导致在执行所有后续的查询时都需要重新编译。
在任务关键型服务器中使用查询存储
全局跟踪标志 7745 和 7752 可用于使用查询存储来提高数据库的可用性。 有关更多信息，请参见跟踪标记。
从 SQL Server 2019 (15.x) 开始，此行为由引擎控制，跟踪标志 7752 不再有效。
重要
如果仅对 SQL Server 2016 (13.x) 中的实时工作负载见解使用查询存储，请尽快安装 SQL Server 2016 (13.x) SP2 CU2 (KB 4340759) 中的性能可伸缩性改进。 如果没有这些改进，则当数据库处于繁重的工作负载下时，可能会发生旋转锁争用，并且服务器性能可能会变慢。 特别是，你可能会发现 QUERY_STORE_ASYNC_PERSIST 旋转锁或 SPL_QUERY_STORE_STATS_COOKIE_CACHE 旋转锁上出现繁重的争用情况。 应用此改进后，查询存储将不再导致旋转锁争用。
重要
如果你在 SQL Server（SQL Server 2016 (13.x) 到 SQL Server 2017 (14.x)）中使用查询存储来获取实时工作负载见解，请尽快在 SQL Server 2016 （13.x) SP2 CU15、SQL Server 2017 (14.x) CU23 和 SQL Server 2019 (15.x) CU9 中安装性能可伸缩性改进功能。 如果没有此改进，则当数据库处于繁重的即席工作负载下时，查询存储可能会占用大量内存，并且服务器性能可能会变慢。 应用此改进后，查询存储会对其各个组件可使用的内存量施加内部限制，并且可以自动将操作模式更改为只读，直到有足够的内存返回到数据库引擎。 请注意，不会记录查询存储内部内存限制，因为它们随时可能更改。
在 Azure SQL 数据库活动异地复制中使用查询存储
Azure SQL 数据库的辅助活动异地复制上的查询存储将是主要副本上的活动的只读副本。
避免在使用 Azure SQL 数据库异地复制时出现不匹配的层。 辅助数据库在大小方面应与主数据库相同或相近，并且应与主数据库处于同一服务层。 在 sys.dm_db_wait_stats 中查找 HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO 等待类型，该类型表示由于辅助延迟而导致主副本上的事务日志速率受限。
若要详细了解如何估计和配置活动异地复制的辅助 Azure SQL 数据库的大小，请参阅配置辅助数据库。
始终根据工作负载调整查询存储
本文扩展了有关配置和管理查询存储的最佳做法和建议：管理查询存储的最佳做法。
使用查询存储优化性能
SQL Server 查询存储功能提供在工作负载中发现和优化查询的功能，无论是通过 SQL Server Management Studio 可视化界面还是 T-SQL 查询。 本文详细介绍了如何获取可操作的信息来提高数据库中的查询性能，包括如何根据查询的使用情况统计信息和强制计划来标识查询。 还可以使用查询存储提示功能来识别查询并调整其查询计划，而无需更改应用程序代码。
性能优化示例查询
查询存储将保存整个查询过程中的编译历史记录和运行时度量，使你能询问有关工作负载的问题。
下面的示例查询可能对你的性能基线和查询性能调查有所帮助：
在数据库上执行的最后一个查询
在数据库上执行的最后 n 个查询：
| SELECT TOP 10 qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, rs.last_execution_time FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id ORDER BY rs.last_execution_time DESC; |
执行计数
每个查询的执行数量：
| SELECT q.query_id, qt.query_text_id, qt.query_sql_text, SUM(rs.count_executions) AS total_execution_count FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text ORDER BY total_execution_count DESC; |
最长平均执行时间
过去一小时内具有最长平均执行时间的查询数量：
| SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime, rs.last_execution_time FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE()) ORDER BY rs.avg_duration DESC; |
最大平均物理 I/O 读取数
在相应的平均行计数和执行计数下，过去 24 小时内具有最大平均物理 I/O 读取数的查询数量：
| SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id, rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE()) ORDER BY rs.avg_physical_io_reads DESC; |
具有多个计划的查询
这些查询特别有趣，因为计划选择更改可能造成它们的性能回归。 以下查询将这些查询和所有计划一同进行了标识：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | WITH Query_MultPlans AS ( SELECT COUNT(*) AS cnt, q.query_id FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON p.query_id = q.query_id GROUP BY q.query_id HAVING COUNT(distinct plan_id) > 1 ) SELECT q.query_id, object_name(object_id) AS ContainingObject, query_sql_text, plan_id, p.query_plan AS plan_xml, p.last_compile_start_time, p.last_execution_time FROM Query_MultPlans AS qm JOIN sys.query_store_query AS q ON qm.query_id = q.query_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id ORDER BY query_id, plan_id; |
最长等待持续时间
此查询将返回具有最长等待持续时间的前 10 个查询：
| SELECT TOP 10 qt.query_text_id, q.query_id, p.plan_id, sum(total_query_wait_time_ms) AS sum_total_wait_ms FROM sys.query_store_wait_stats ws JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id GROUP BY qt.query_text_id, q.query_id, p.plan_id ORDER BY sum_total_wait_ms DESC; |
备注
在 Azure Synapse Analytics 中，本部分中的查询存储示例查询受支持，但等待统计信息除外，该信息在 Azure Synapse Analytics 查询存储 DMV 中不可用。
最近具有性能回归的查询
以下查询示例返回了其执行时间因计划选择更改而在过去 48 小时内翻倍的所有查询。 此查询会并排比较所有运行时统计信息时间间隔：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | SELECT qt.query_sql_text, q.query_id, qt.query_text_id, rs1.runtime_stats_id AS runtime_stats_id_1, rsi1.start_time AS interval_1, p1.plan_id AS plan_1, rs1.avg_duration AS avg_duration_1, rs2.avg_duration AS avg_duration_2, p2.plan_id AS plan_2, rsi2.start_time AS interval_2, rs2.runtime_stats_id AS runtime_stats_id_2 FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p1 ON q.query_id = p1.query_id JOIN sys.query_store_runtime_stats AS rs1 ON p1.plan_id = rs1.plan_id JOIN sys.query_store_runtime_stats_interval AS rsi1 ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id JOIN sys.query_store_plan AS p2 ON q.query_id = p2.query_id JOIN sys.query_store_runtime_stats AS rs2 ON p2.plan_id = rs2.plan_id JOIN sys.query_store_runtime_stats_interval AS rsi2 ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE()) AND rsi2.start_time > rsi1.start_time AND p1.plan_id <> p2.plan_id AND rs2.avg_duration > 2*rs1.avg_duration ORDER BY q.query_id, rsi1.start_time, rsi2.start_time; |
如果想查看所有回归的性能（而不仅是与计划选择更改相关的回归），请从前一个查询中删除条件 AND p1.plan_id <> p2.plan_id 。
具有历史性能回归的查询
下一个查询将最近的执行与历史执行进行比较，从而根据执行周期比较查询执行。 在此特定示例中，查询对比了最近时期（1 小时）和历史时期（过去一天）中的执行，并标识了引入 additional_duration_workload 的查询。 此度量的计算方式是最近平均执行和历史平均执行之差，再乘以最近执行数量。 它实际上表示相对于历史记录，引入了多少额外的持续时间最近执行：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | --- "Recent" workload - last 1 hour DECLARE @recent_start_time datetimeoffset; DECLARE @recent_end_time datetimeoffset; SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME()); SET @recent_end_time = SYSUTCDATETIME(); --- "History" workload DECLARE @history_start_time datetimeoffset; DECLARE @history_end_time datetimeoffset; SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME()); SET @history_end_time = SYSUTCDATETIME(); WITH hist AS ( SELECT p.query_id query_id, ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration, SUM(rs.count_executions) AS count_executions, COUNT(distinct p.plan_id) AS num_plans FROM sys.query_store_runtime_stats AS rs JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id WHERE (rs.first_execution_time >= @history_start_time AND rs.last_execution_time < @history_end_time) OR (rs.first_execution_time <= @history_start_time AND rs.last_execution_time > @history_start_time) OR (rs.first_execution_time <= @history_end_time AND rs.last_execution_time > @history_end_time) GROUP BY p.query_id ), recent AS ( SELECT p.query_id query_id, ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration, SUM(rs.count_executions) AS count_executions, COUNT(distinct p.plan_id) AS num_plans FROM sys.query_store_runtime_stats AS rs JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id WHERE (rs.first_execution_time >= @recent_start_time AND rs.last_execution_time < @recent_end_time) OR (rs.first_execution_time <= @recent_start_time AND rs.last_execution_time > @recent_start_time) OR (rs.first_execution_time <= @recent_end_time AND rs.last_execution_time > @recent_end_time) GROUP BY p.query_id ) SELECT results.query_id AS query_id, results.query_text AS query_text, results.additional_duration_workload AS additional_duration_workload, results.total_duration_recent AS total_duration_recent, results.total_duration_hist AS total_duration_hist, ISNULL(results.count_executions_recent, 0) AS count_executions_recent, ISNULL(results.count_executions_hist, 0) AS count_executions_hist FROM ( SELECT hist.query_id AS query_id, qt.query_sql_text AS query_text, ROUND(CONVERT(float, recent.total_duration/ recent.count_executions-hist.total_duration/hist.count_executions) *(recent.count_executions), 2) AS additional_duration_workload, ROUND(recent.total_duration, 2) AS total_duration_recent, ROUND(hist.total_duration, 2) AS total_duration_hist, recent.count_executions AS count_executions_recent, hist.count_executions AS count_executions_hist FROM hist JOIN recent ON hist.query_id = recent.query_id JOIN sys.query_store_query AS q ON q.query_id = hist.query_id JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id ) AS results WHERE additional_duration_workload > 0 ORDER BY additional_duration_workload DESC OPTION (MERGE JOIN); |
维护查询性能稳定性
对于执行多次的查询，你可能注意到 SQL Server 使用了会导致不同资源利用率和持续时间的不同计划。 借助查询存储，可以检测到查询性能何时回归，并确定在感兴趣的时间段内的最优计划。 然后你可以对未来的查询执行强制执行此最优计划。
你还可以使用参数（自动参数化或手动参数化）来标识某一查询内不一致的查询性能。 你可以在不同计划中标识出对所有或大多数参数值而言足够快和最佳的计划，并强制执行此计划，为更大范围的用户场景保持可预测的性能。
强制执行查询计划（应用强制策略）
当强制执行某一查询的计划时，SQL Server 尝试在优化器中强制执行该计划。 如果计划强制实施失败，将触发 XEvent，并指示优化器正常优化。
SQL
| EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49; |
在使用 sp_query_store_force_plan 时，你只可以强制执行查询存储记录为该查询计划的那些计划。 换句话说，可用于查询的计划只有那些在查询存储处于活动状态时已用于执行该查询的计划。
备注
Azure Synapse Analytics 不支持在查询存储中强制执行计划。
计划强制支持快进和静态游标
从 SQL Server 2019 (15.x) 和 Azure SQL 数据库（所有部署模型）开始，查询数据存储支持为快进和静态 Transact-SQL 及 API 游标强制执行查询执行计划。 通过 sp_query_store_force_plan 或通过 SQL Server Management Studio 查询存储报表支持强制执行。
删除为查询强制执行的计划
若要再次依靠 SQL Server 查询优化器来计算最佳查询计划，请使用 sp_query_store_unforce_plan 来取消强制执行为查询选定的计划。
SQL
| EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49; |
管理查询存储的最佳做法
本文概述了 SQL Server 查询存储的管理及其相关功能。
在 SQL Server 2022 (16.x) 中，所有新创建的 SQL Server 数据库默认启用查询存储，以便更好地跟踪性能历史记录、排查查询计划相关问题并启用新的查询处理器功能。
Azure SQL 数据库中的查询存储默认值
本部分介绍 Azure SQL 数据库中的最佳配置默认值，这些默认值旨在确保查询存储以及依赖功能能够可靠运行。 默认配置已针对持续数据收集操作进行优化，即，在 OFF/READ_ONLY 状态下花费最少的时间。 有关所有可用的查询存储选项的详细信息，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。
展开表
| 配置 | 说明 | 默认 | 注释 |
|---|
| MAX_STORAGE_SIZE_MB | 指定 Query Store 在客户数据库中占用的数据空间的限制 | 100，SQL Server 2019 (15.x) 之前的版本 1000，从 SQL Server 2019 (15.x) 开始 | 对新数据库强制实施 |
| INTERVAL_LENGTH_MINUTES | 定义聚合和持久化查询计划收集运行时统计信息的时段大小。 每个活动查询计划将为此配置定义的时间段包含最多一行 | 60 | 对新数据库强制实施 |
| STALE_QUERY_THRESHOLD_DAYS | 基于时间的清理策略，控制持久化运行时统计信息和非活动查询的保留期 | 30 | 对新数据库和使用以前的默认值 (367) 的数据库强制实施 |
| SIZE_BASED_CLEANUP_MODE | 指定当 Query Store 数据大小接近限制时是否自动清理数据 | AUTO | 对所有数据库强制实施 |
| QUERY_CAPTURE_MODE | 指定是要跟踪所有查询，还是只跟踪一部分查询 | AUTO | 对所有数据库强制实施 |
| DATA_FLUSH_INTERVAL_SECONDS | 指定捕获的运行时统计信息在刷新到磁盘之前，保留在内存中的最大期限 | 900 | 对新数据库强制实施 |
重要
在查询存储的最终激活阶段，系统会在 Azure SQL 数据库中自动应用这些默认值。 启用后，Azure SQL 数据库不会更改客户设置的配置值，除非这些值对主要工作负载或查询存储的可靠运行造成负面影响。
备注
无法在 Azure SQL 数据库的单一数据库和弹性池中禁用查询存储。 执行 ALTER DATABASE [database] SET QUERY_STORE = OFF 将返回警告“'QUERY_STORE=OFF' is not supported in this version of SQL Server.”
如果想要保持使用自定义设置，请结合 Query Store 选项使用 ALTER DATABASE，将配置还原到以前的状态。 请查看查询存储最佳做法，了解如何选择最佳的配置参数。
设置最佳查询存储捕获模式
在 Query Store 中保留最相关数据。 下表描述了每个查询存储捕获模式的典型方案：
展开表
| Query Store 捕获模式 | 场景 |
|---|
| 全部 | 对工作负载进行彻底地分析，分析所有查询的形状及其执行频率和其他统计信息。 识别工作负荷中的新查询。 检测是否使用即席查询来识别用户或自动参数化的机会。 注意：这是 SQL Server 2016 (13.x) 和 SQL Server 2017 (14.x) 中的默认捕获模式。 |
| Auto | 关注相关且可操作的查询。 例如，那些定期执行的查询或资源消耗很大的查询。 注意：在 SQL Server 2019 (15.x) 及更高版本中，这是默认捕获模式。 |
| 无 | 你已经捕获了需要在运行时监视的查询集，因此需消除其他查询可能会带来的干扰。 “无”适用于测试和基准测试环境。 “无”也适用于需要提供已配置的 Query Store 配置来监视其应用程序工作负荷的软件供应商。 在使用“无”时应格外小心，因为可能无法跟踪和优化重要的新查询。 避免使用“无”，除非你的特定方案需要使用它。 |
| 自定义 | SQL Server 2019 (15.x) 在 ALTER DATABASE ... SET QUERY_STORE 命令下引入了自定义捕获模式。 虽然“Auto”是默认设置且建议使用，但如果担心查询存储可能会引入开销，数据库管理员可以使用自定义捕获策略进一步优化查询存储捕获行为。 有关详细信息和建议，请参阅本文后面的自定义捕获策略。 有关此语法的详细信息，请参阅 ALTER DATABASE SET 选项。 |
备注
当查询存储捕获模式设置为“全部”、“自动”或“自定义”时，始终捕获游标、存储过程中的查询和本机编译的查询。 若要捕获本机编译的查询，请使用 sys.sp_xtp_control_query_exec_stats 启用每个查询统计信息的收集。
在 Query Store 中保留最相关数据
将查询存储配置为只包含最相关的数据，这样在持续运行的时候对常规工作负载的影响最小，方便进行故障排除。
下表提供最佳实践：
展开表
| 最佳做法 | 设置 |
|---|
| 对保留的历史数据进行限制。 | 配置基于时间的策略以激活自动清理功能。 |
| 筛选掉不相关的查询。 | 将“查询存储捕获模式”配置为“自动”。 |
| 达到最大大小时，删除不太相关的查询。 | 激活基于大小的清理策略。 |
自定义捕获策略
启用 CUSTOM 查询存储捕获模式后，可以在新的“查询存储捕获策略设置”下使用其他查询存储配置，以微调特定服务器中的数据收集。
新的自定义设置定义在内部捕获策略时间阈值期间执行的操作。 这是评估配置条件的时间边界，如果所有值为 true，则查询存储可以捕获查询。
查询存储捕获模式指定了查询存储的查询捕获策略。
- All：捕获所有查询。 此选项是 SQL Server 2016 (13.x) 和 SQL Server 2017 (14.x) 中的默认选项。
- Auto：忽略不太频繁的查询以及编译和执行持续时间不长的查询。 执行计数、编译和运行时持续时间的阈值由内部决定。 从 SQL Server 2019 (15.x) 开始，这是默认选项。
- None：查询存储停止捕获新查询。
- Custom：支持额外控件和微调数据收集策略功能。 新的自定义设置定义在内部捕获策略时间阈值期间执行的操作。 这是评估配置条件的时间边界，如果所有值为 true，则查询存储可以捕获查询。
在下列情况下，应考虑为环境优化适当的自定义捕获策略：
使用最新版本的 SQL Server Management Studio (SSMS)
若要查看 Management Studio 中的当前设置：
- 在 SQL Server Management Studio 对象资源管理器中，右键单击数据库。
- 选择“属性”。
- 选择查询存储。 在查询存储页面上，验证操作模式（请求）是否为 Read write。
- 将查询存储捕获模式更改为 Custom。
- 注意，查询存储捕获策略下的四个捕获策略字段现已启用并可配置。
自定义捕获策略示例
以下示例将 QUERY_CAPTURE_MODE 设置为 AUTO 并设置自定义捕获模式。 以下每一项都将自定义捕获策略设置为 SQL Server 2022 (16.x) 中的默认值。 考虑调整这些值，以减少捕获的查询数，从而减少查询存储的磁盘占用空间。 建议按小增量逐步更改这些值。
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ALTER DATABASE [QueryStoreDB] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ), DATA_FLUSH_INTERVAL_SECONDS = 900, MAX_STORAGE_SIZE_MB = 1000, INTERVAL_LENGTH_MINUTES = 60, SIZE_BASED_CLEANUP_MODE = AUTO, QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY = ( STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS, EXECUTION_COUNT = 30, TOTAL_COMPILE_CPU_TIME_MS = 1000, TOTAL_EXECUTION_CPU_TIME_MS = 100 ) ); |
以下示例查询将更改现有查询存储，以使用自定义捕获策略来替代 EXECUTION_COUNT 和 TOTAL_COMPILE_CPU_TIME_MS 的默认设置。
SQL
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE = ON ( QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY = ( EXECUTION_COUNT = 100, TOTAL_COMPILE_CPU_TIME_MS = 10000 ) ); |
查询存储最大大小
从 SQL Server 2019 (15.x) 开始，查询存储的默认最大大小值为 1000 MB。 在以前的版本中，默认值为 100 MB。 在具有多个唯一查询计划的忙碌数据库中，增加查询存储的最大大小限制较为合适。 调整捕获策略（见上一节）是限制查询存储的磁盘大小并防止查询存储进入 READ_ONLY 模式的重要考虑因素。 当查询存储收集查询、执行计划和统计信息时，其在数据库中的大小会一直增长，直至达到此限制。 达到此限制后，Query Store 会自动将操作模式更改为 READ_ONLY，并停止收集新数据，这意味着你的性能分析自此不再精确。
- 在 SQL Server 和 Azure SQL 托管实例中，不会严格执行限制
MAX_STORAGE_SIZE_MB。 - 在 Azure SQL 数据库中，允许的最大
MAX_STORAGE_SIZE_MB 值为 10,240 MB。
仅当查询存储将数据写入磁盘时才检查存储大小。 此间隔由 DATA_FLUSH_INTERVAL_SECONDS 选项或 Management Studio 查询存储对话框选项“数据刷新间隔”设置。
有关详细信息，请参阅 ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB。
数据刷新间隔（分钟）
数据刷新间隔定义了收集的运行时统计信息保存到磁盘之前的频率。 在 SQL Server Management Studio 中，该值以分钟为单位，但在 Transact-SQL 中，该值以秒为单位表示。 默认值为 15 分钟（900 秒）。
如果出现故障转移或关闭命令，使用跟踪标志 7745 会阻止查询存储数据写入磁盘。 有关详细信息，请参阅在任务关键型服务器中使用查询存储。
修改查询存储默认值
根据工作负荷和性能故障排除要求来配置 Query Store。 默认参数是启动的理想参数，但应监视查询存储在一定时段内的行为表现，并对其配置进行相应的调整。
查看查询存储当前设置
查看 SQL Server Management Studio (SSMS) 或 T-SQL 中的当前查询存储设置。
使用最新版本的 SQL Server Management Studio (SSMS)
若要查看 Management Studio 中的当前设置：
- 在 SQL Server Management Studio 对象资源管理器中，右键单击数据库。
- 选择“属性”。
- 选择查询存储。
以下脚本将设置新的“最大大小 (MB)”值：
SQL
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024); |
使用 SQL Server Management Studio 或 Transact-SQL 为“数据刷新间隔”设置不同的值：
SQL
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900); |
统计信息收集间隔：定义收集的运行时统计信息的粒度级别（以分钟为单位）。 默认值为 60 分钟。 如果需要更细的粒度或更短的时间来检测和缓解问题，可考虑使用较小的值。 请记住，该值会直接影响查询存储数据的大小。 使用 SQL Server Management Studio 或 Transact-SQL 为“统计信息收集间隔”设置不同的值：
SQL
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60); |
过时查询阈值（天）：基于时间的清除策略，用于控制持久化运行时统计信息和非活动查询的保持期（以天为单位）。 查询存储默认配置为将数据保留 30 天，这对于你的方案来说可能过长。
避免保留你并不打算使用的历史数据。 这样可以减少变为只读状态的次数。 查询存储数据的大小以及检测和解决问题的时间将会变得更可预测。 使用 Management Studio 或以下脚本配置基于时间的清理策略：
SQL
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90)); |
基于大小的清理模式：指定在查询存储数据大小达到限制时，是否启用自动数据清理功能。 请激活基于大小的清理功能，确保查询存储始终以读写模式运行并收集最新数据。 在繁重的工作负载下，不能保证查询存储清理会始终将数据大小保持在限制范围内。 自动数据清除可能会落后并切换（暂时）到只读模式。
SQL
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO); |
Query Store 捕获模式：指定查询存储的查询捕获策略。
当查询存储捕获模式设置为“全部”、“自动”或“自定义”时，始终捕获游标、存储过程中的查询和本机编译的查询。 若要捕获本机编译的查询，请使用 sys.sp_xtp_control_query_exec_stats 启用每个查询统计信息的收集。
以下脚本将 QUERY_CAPTURE_MODE 设置为 AUTO：
SQL
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO); |
示例
以下示例将 QUERY_CAPTURE_MODE 设置为 AUTO，并在 SQL Server 2016 (13.x) 中设置其他建议选项：
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ), DATA_FLUSH_INTERVAL_SECONDS = 900, QUERY_CAPTURE_MODE = AUTO, MAX_STORAGE_SIZE_MB = 1000, INTERVAL_LENGTH_MINUTES = 60 ); |
以下示例将 QUERY_CAPTURE_MODE 设置为 AUTO，并在 SQL Server 2017 (14.x) 中设置其他建议选项以包括等待统计信息：
SQL
| ALTER DATABASE [QueryStoreDB] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ), DATA_FLUSH_INTERVAL_SECONDS = 900, QUERY_CAPTURE_MODE = AUTO, MAX_STORAGE_SIZE_MB = 1000, INTERVAL_LENGTH_MINUTES = 60, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON ); |
以下示例将 CUSTOM 捕获策略设置为 SQL Server 2019 (15.x) 默认值，而不是新的默认 AUTO 捕获模式。 有关自定义捕获策略选项和默认值的详细信息，请参阅 。
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | ALTER DATABASE [QueryStoreDB] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ), DATA_FLUSH_INTERVAL_SECONDS = 900, MAX_STORAGE_SIZE_MB = 1000, INTERVAL_LENGTH_MINUTES = 60, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON, QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY = ( STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS, EXECUTION_COUNT = 30, TOTAL_COMPILE_CPU_TIME_MS = 1000, TOTAL_EXECUTION_CPU_TIME_MS = 100 ) ); |
查询存储维护
本部分提供一些有关如何管理查询存储功能本身的准则。
查询存储状态
查询存储将其数据存储在用户数据库内，正因为此，它具有大小限制（使用 MAX_STORAGE_SIZE_MB 进行配置）。 如果查询存储中的数据命中该限制，则查询存储将自动从读写状态更改为只读状态，并停止收集新数据。
查询 sys.database_query_store_options ，以确定当前查询存储是否可用，以及当前是否在收集运行时状态。
SQL
| SELECT actual_state, actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options; |
查询存储状态是由 actual_state 列决定的。 如果不处于所需状态，请查看 readonly_reason 列，了解详细信息。 当查询存储大小超过配额时，该功能将切换到 read_only 模式，并提供原因。 有关原因的信息，请参阅 sys.database_query_store_options (Transact-SQL)。
获取查询存储选项
若要了解查询存储状态的相关详细信息，请在用户数据库中执行以下操作。
SQL
| SELECT * FROM sys.database_query_store_options; |
设置查询存储间隔
你可以覆盖用于聚合查询运行时统计信息的时间间隔（默认值为 60 分钟）。 通过 sys.database_query_store_options 视图公开时间间隔的新值。
SQL
| ALTER DATABASE <database_name> SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15); |
INTERVAL_LENGTH_MINUTES 不允许使用任意值。 可以使用下列间隔之一：1、5、10、15、30、60 或 1440 分钟。
备注
对于 Azure Synapse Analytics，不支持自定义查询存储配置选项，如本部分所示。
查询存储空间使用情况
若要检查当前的查询存储大小和限制，请在用户数据库中执行以下语句。
SQL
| SELECT current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options; |
如果查询存储已满，请使用以下语句来扩展存储。
SQL
| ALTER DATABASE <database_name> SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>); |
获取查询存储选项
你可以使用单个 ALTER DATABASE 语句同时设置多个查询存储选项。
SQL
| ALTER DATABASE <database name> SET QUERY_STORE ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 3000, MAX_STORAGE_SIZE_MB = 500, INTERVAL_LENGTH_MINUTES = 15, SIZE_BASED_CLEANUP_MODE = AUTO, QUERY_CAPTURE_MODE = AUTO, MAX_PLANS_PER_QUERY = 1000, WAIT_STATS_CAPTURE_MODE = ON ); |
有关配置选项的完整列表，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。
清理空间
查询存储时间间隔表是在数据库创建期间在 PRIMARY 文件组中创建的，且之后不可更改此配置。 如果空间已用完，可能需要使用以下语句来清除更旧的查询存储数据。
SQL
| ALTER DATABASE <db_name> SET QUERY_STORE CLEAR; |
或者，你可以只清理临时查询数据，因为此数据与查询优化和计划分析的相关性更低，但却占用了大量空间。
在 Azure Synapse Analytics 中，清除查询存储不可用。 系统会自动保留过去 7 天的数据。
删除临时查询
这将从查询存储中清除即席查询和内部查询，以便查询存储不会耗尽空间并删除我们真正需要跟踪的查询。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | SET NOCOUNT ON -- This purges adhoc and internal queries from -- the Query Store in the current database -- so that the Query Store does not run out of space -- and remove queries we really need to track DECLARE @id int; DECLARE adhoc_queries_cursor CURSOR FOR SELECT q.query_id FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_id JOIN sys.query_store_plan AS p ON p.query_id = q.query_id JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id WHERE q.is_internal_query = 1 -- is it an internal query then we dont care to keep track of it OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it GROUP BY q.query_id HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE()) -- if it has been more than 5 minutes since the adhoc query ran ORDER BY q.query_id; OPEN adhoc_queries_cursor ; FETCH NEXT FROM adhoc_queries_cursor INTO @id; WHILE @@fetch_status = 0 BEGIN PRINT 'EXEC sp_query_store_remove_query ' + str(@id); EXEC sp_query_store_remove_query @id; FETCH NEXT FROM adhoc_queries_cursor INTO @id; END CLOSE adhoc_queries_cursor; DEALLOCATE adhoc_queries_cursor; |
你可以使用其他逻辑来定义自己的过程，以清理不再需要的数据。
以上示例使用 sp_query_store_remove_query 扩展存储过程来删除不必要的数据。 也可执行以下操作：
- 使用
sp_query_store_reset_exec_stats 清除给定计划的运行时统计信息。 - 使用
sp_query_store_remove_plan 删除单个计划。
查询存储提示
本文概述了如何使用查询存储应用查询提示。 查询存储提示提供了一种易于使用的方法，可在不更改应用程序代码的情况下制定查询计划。
Azure SQL 数据库和 Azure SQL 托管实例中提供查询存储提示功能。 查询存储提示也是 SQL Server 2022 (16.x) 中引入 SQL Server 的一项功能。
由于 SQL Server 查询优化器通常会为查询选择最佳执行计划，我们建议资深开发人员和数据库管理员仅在不得已时使用提示。 有关详细信息，请参阅查询提示。
观看此视频，大致了解查询存储提示：
概述
理想情况下，查询优化器为查询选择最佳执行计划。
如果没有选择最佳计划，开发人员或 DBA 可能希望针对特定条件进行手动优化。 查询提示通过 OPTION 子句指定，可用于影响查询执行行为。 虽然查询提示有助于为各种性能相关问题提供本地化解决方案，但它们要求重写原始查询文本。 数据库管理员和开发人员可能并不总是能够直接更改 Transact-SQL 代码来注入查询提示。 Transact-SQL 可硬编码到应用程序中，也可由应用程序自动生成。 以前，开发人员可能必须依赖计划指南，这可能用起来很复杂。
有关可应用哪些查询提示的信息，请参阅支持的查询提示。
何时使用查询存储提示
如名称所示，此功能扩展并依赖于查询存储。 查询存储可捕获查询、执行计划和关联的运行时统计信息。 查询存储可极大地简化整体性能优化客户体验。 SQL Server 2016 (13.x) 首先引入了查询存储，现在默认在 SQL Server 2022 (16.x)、Azure SQL 托管实例 和 Azure SQL 数据库中启用。
首先执行查询，再由查询存储捕获。 然后，DBA 对查询创建一个查询存储提示。 接着，使用查询存储提示执行查询。
有关查询存储提示可在哪些方面帮助解决查询级别的性能问题的示例：
- 在每次执行时重新编译查询。
- 限制批量插入操作的内存授予上限。
- 限制更新统计信息时的最大并行度。
- 使用哈希联接而不是嵌套循环联接。
- 对特定查询使 兼容性级别 110，同时将数据库中其他所有内容都保留为兼容级别 150。
- 禁用 SELECT TOP 查询的行目标优化。
若要使用查询存储提示，请执行以下操作：
确定你希望修改的查询语句的查询存储
。 可通过多种方式执行此操作：
- 查询查询存储目录视图。
- 使用 SQL Server Management Studio 内置查询存储报表。
- 使用适用于 Azure SQL 数据库的 Azure 门户 Query Performance Insight。
使用你想要应用于查询的 query_id 和查询提示字符串执行 sys.sp_query_store_set_hints。 此字符串可包含一个或多个查询提示。 有关完整信息，请参阅 sys.sp_query_store_set_hints。
创建后，查询存储提示将持久保存，在重启和故障转移后仍然存在。 查询存储提示会替代硬编码的语句级别提示和现有的计划指南提示。
如果查询提示与查询优化可能的结果相冲突，则其将不会阻止查询执行并且不会应用提示。 如果提示导致查询失败，则会忽略提示，并可在 sys.query_store_query_hints 中查看最新的失败详细信息。
查询存储提示系统存储过程
若要创建或更新提示，请使用 sys.sp_query_store_set_hints。 在有效字符串格式 N'OPTION (...)' 中指定提示。
- 创建查询存储提示时，如果特定
query_id 没有查询存储提示，会创建新的查询存储提示。 - 创建或更新查询存储提示时，如果特定
query_id 已存在查询存储提示，则提供的最后一个值将替代之前为关联查询指定的值。 如果 query_id 不存在，将引发错误。
备注
有关支持的提示的完整列表，请参阅 sys.sp_query_store_set_hints。
若要删除与 query_id 关联的提示，请使用 sys.sp_query_store_clear_hints。
执行计划 XML 特性
应用提示后，以下结果集将以 XML 格式显示在执行计划的 StmtSimple 元素中：
展开表
| Attribute | 描述 |
|---|
QueryStoreStatementHintText | 应用于查询的实际查询存储提示 |
QueryStoreStatementHintId | 查询提示的唯一标识符 |
QueryStoreStatementHintSource | 查询存储提示的源（例如“用户”） |
备注
这些 XML 元素通过 Transact-SQL 命令 SET STATISTICS XML 和 SET SHOWPLAN XML 的输出提供。
查询存储提示和功能互操作性
- 查询存储提示会替代其他硬编码语句级别提示和计划指南。
- 查询始终执行。 对立的查询存储提示会被忽略，否则会导致错误。
- 如果查询存储提示相矛盾，SQL Server 不会阻止查询执行，也不会应用查询存储提示。
- 简单参数化 - 符合简单参数化条件的语句不支持查询存储提示。
- 强制参数化 - RECOMPILE 提示与数据库级别的强制参数化集不兼容。 如果数据库具有强制参数化集，并且 RECOMPILE 提示是查询存储中提示字符串集的一部分，则 SQL Server 会忽略 RECOMPILE 提示，并将应用任何其他提示（如果已使用）。
- 此外，SQL Server 会发出警告（错误代码 12461），指出 RECOMPILE 提示已被忽略。
- 有关强制参数化用例注意事项的详细信息，请参阅强制参数化使用指南。
- 手动创建的查询存储提示无需清理。 自动保留捕获策略不会从查询存储中清理提示和查询。
- 查询可以被用户手动移除，这也将移除关联的查询存储提示。
- CE 反馈自动生成的查询存储提示会被自动保留的捕获策略清理。
- DOP 反馈和内存授予反馈可在不使用查询存储提示的情况下塑造查询行为。 通过自动保留捕获策略清理查询时，DOP 反馈和内存授予反馈数据也会被清理。
- 可以手动创建 CE 反馈实现的同一查询存储提示，然后带有该提示的查询将不再会被捕获策略自动保留清理。
查询存储提示和可用性组
有关详细信息，请参阅次要副本的查询存储。
查询存储提示最佳做法
在评估针对潜在新查询存储提示的查询之前，请完成索引和统计信息维护。
在使用查询存储提示之前，请在最新的
兼容性级别
上测试应用数据库。
- 例如，在 SQL Server 2022 (16.x)（兼容级别 160）中引入了参数敏感计划 (PSP) 优化，该优化使用每个查询的多个活动计划来解决不均匀的数据分布。 如果环境无法使用最新的兼容性级别，则使用 RECOMPILE 提示的查询存储提示可以在任何支持的兼容性级别上使用。
查询存储提示会替代 SQL Server 查询计划行为。 建议仅在需要解决与性能相关的问题时才使用查询存储提示。
建议在数据分布发生变化的任何时间和数据库迁移项目期间重新评估查询存储提示、语句级提示、计划指南和查询存储强制计划。 数据分布的变化可能会导致查询存储提示生成欠佳的执行计划。
示例
A. 查询存储提示演示
下面演练了 Azure SQL 数据库中的查询存储提示，它通过 BACPAC 文件 (.bacpac) 使用导入的数据库。 若要了解如何将新的数据库导入到 Azure SQL 数据库服务器，请参阅快速入门：将 BACPAC 文件导入数据库。
Transact-SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | -- ************************************************************************ -- -- Query Store hints demo -- Demo uses "PropertyMLS" database which can be imported from BACPAC here: -- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store -- Email QSHintsFeedback@microsoft.com for questions\feedback -- ************************************************************************ -- /* Demo prep, connect to the PropertyMLS database */ ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR; ALTER DATABASE CURRENT SET QUERY_STORE = ON; ALTER DATABASE CURRENT SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL); GO -- Should be READ_WRITE SELECT actual_state_desc FROM sys.database_query_store_options; GO /* You can verify Query Store Hints in sys.query_store_query_hints. Checking if any already exist (should be none). */ SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc FROM sys.query_store_query_hints; GO /* The PropertySearchByAgent stored procedure has a parameter used to filter AgentId. Looking at the statistics for AgentId, you will see that there is a big skew for AgentId 101. */ SELECT hist.range_high_key AS [AgentId], hist.equal_rows FROM sys.stats AS s CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist WHERE s.[name] = N'NCI_Property_AgentId'; -- Show actual query execution plan to see plan compiled. -- Agent with many properties will have a scan with parallelism. EXEC [dbo].[PropertySearchByAgent] 101; -- Agents with few properties still re-use this plan (assuming no recent plan eviction). EXEC [dbo].[PropertySearchByAgent] 4; /* Now let's find the query_id associated with this query. */ SELECT query_sql_text, q.query_id FROM sys.query_store_query_text qt INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%'; GO /* We can set the hint associated with the query_id returned in the previous result set, as below. Note, we can designate one or more query hints */ EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)'; GO /* You can verify Query Store Hints in sys.query_store_query_hints */ SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc FROM sys.query_store_query_hints; GO -- Execute both at the same time and show actual query execution plan. -- You should see two different plans, one for AgentId 101 and one for AgentId 4. EXEC [dbo].[PropertySearchByAgent] 101; EXEC [dbo].[PropertySearchByAgent] 4; GO SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc FROM sys.query_store_query_hints; GO /* We can remove the hint using sp_query_store_clear_query_hints */ EXEC sp_query_store_clear_hints @query_id = 10; GO /* That Query Store Hint is now removed */ SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc FROM sys.query_store_query_hints; GO -- Execute both at the same time and show actual query execution plan. -- You should see one plan again. EXEC [dbo].[PropertySearchByAgent] 101; EXEC [dbo].[PropertySearchByAgent] 4; GO |
B. 在查询存储中标识查询
以下示例查询 sys.query_store_query_text 和 sys.query_store_query，以返回执行的查询文本片段的 query_id。
在此演示中，我们尝试优化的查询位于 SalesLT 示例数据库中：
SQL
| SELECT * FROM SalesLT.Address as A INNER JOIN SalesLT.CustomerAddress as CA on A.AddressID = CA.AddressID WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC; |
查询存储不会立即将查询数据反映到其系统视图中。
在查询存储系统目录视图中标识查询：
SQL
| SELECT q.query_id, qt.query_sql_text FROM sys.query_store_query_text qt INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id WHERE query_sql_text like N'%PostalCode =%' AND query_sql_text not like N'%query_store%'; GO |
在以下示例中，SalesLT 数据库中的上一个查询示例被标识为 query_id 39。
标识后，应用提示以对 query_id 强制实施最大内存授予大小（以配置的内存限制百分比表示）：
SQL
| EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)'; |
还可使用以下语法应用查询提示，例如强制使用旧版基数估计器的选项：
SQL
| EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))'; |
可使用逗号分隔列表应用多个查询提示：
SQL
| EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))'; |
在查询存储提示中就地查看 query_id 39：
SQL
| SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc FROM sys.query_store_query_hints WHERE query_id = 39; |
最后，使用 sp_query_store_clear_hints 从 query_id 39 中删除提示。
SQL
| EXEC sys.sp_query_store_clear_hints @query_id = 39; |
查询存储提示最佳做法
本文详细介绍了使用查询存储提示的最佳做法。 查询存储提示支持在不修改应用程序代码的情况下调整查询计划形状。
查询存储提示的用例
将以下用例视作查询存储提示的理想用例。 有关详细信息，请参阅何时使用查询存储提示。
注意
由于 SQL Server 查询优化器通常会为查询选择最佳执行计划，我们建议资深开发人员和数据库管理员仅在不得已时使用提示。 有关详细信息，请参阅查询提示。
无法更改代码时
利用查询存储提示，可以影响查询的执行计划，而无需更改应用程序代码或数据库对象。 没有其他功能支持便捷地应用查询提示。
例如，你可以使用查询存储提示来帮助 ETL，而无需重新部署代码。 通过这个 14 分钟的视频了解如何使用查询存储提示改进大容量加载：
查询存储提示是轻量级查询优化方法，但如果查询有问题，应使用重大代码更改来解决。 如果经常发现需要向查询应用查询存储提示，请考虑执行大型查询重写。 SQL Server 查询优化器通常会为查询选择最佳执行计划，我们建议资深开发人员和数据库管理员仅在不得已时使用提示。
有关可应用哪些查询提示的信息，请参阅支持的查询提示。
在高事务负载下或使用任务关键型代码
如果由于运行时间要求高或事务负载高，使得代码更改不可能实现，那么查询存储提示可以快速将查询提示应用于现有查询工作负载。 添加和移除查询存储提示非常简单。
可以将查询存储提示添加到批处理查询，以调整异常工作负载突发时段的性能。
作为计划指南的替代方案
在查询存储提示之前，开发人员必须依赖计划指南来完成类似任务，使用起来非常复杂。 查询存储提示与 SQL Server Management Studio (SSMS) 的查询存储功能相集成，用于直观浏览查询。
使用计划指南时，必须使用查询代码片段搜索所有计划。 查询存储提示功能不需要完全匹配的查询来影响生成的查询计划。 查询存储提示可应用于查询存储数据集中的 query_id。
查询存储提示会替代硬编码的语句级别提示和现有的计划指南。
考虑较新的兼容性级别
例如，如果由于供应商规范或较大的测试延迟而无法使用较新的数据库兼容性级别，查询存储提示可能是一种好方法。 如果数据库可使用更高的兼容性级别，请考虑升级单个查询的数据库兼容性级别，以利用 SQL Server 的最新性能优化和功能。
例如，如果 SQL Server 2022 (16.x) 实例的数据库兼容级别为 140，则仍可以使用查询存储提示来运行兼容级别为 160 的单个查询。 可以使用以下提示：
SQL
| EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))'; |
有关完整教程，请参阅查询存储提示示例。
升级后考虑旧的兼容性级别
查询存储提示可以提供帮助的另一种情况是，在 SQL Server 实例迁移或升级后无法直接修改查询。 使用查询存储提示为查询应用先前的兼容性级别，直到可以重写或以其他方式寻址查询，以便在最新的兼容性级别中运行良好。 使用查询存储的回归查询报告、迁移期间使用查询优化顾问工具或其他查询级别应用程序遥测，识别在更高兼容性级别中回归的离群值查询。 有关兼容性级别之间差异的详细信息，请查看兼容性级别之间的差异。
在对新的兼容性级别进行性能测试并以这种方式部署查询存储提示后，就可以升级整个数据库的兼容性级别，同时将有问题的关键查询保留在先前的兼容性级别上，而无需更改任何代码。
查询存储提示注意事项
部署查询存储提示时，请考虑以下场景。
数据分发更改
计划指南、通过查询存储的强制计划，以及查询存储提示可替代优化器做决策。 查询存储提示现在可能很有利，但在将来可能不会。 例如，如果查询存储提示在以前的数据分发中对查询起到帮助作用，那么在大规模 DML 操作更改了数据时，可能会适得其反。 新的数据分发可能导致优化器作出比提示更好的决策。 该场景是强制执行计划行为最常见的后果。
定期重新评估查询存储提示策略
在以下情况下重新评估现有查询存储提示策略：
- 已知大型数据分发更改后。
- Azure SQL 数据库或托管实例或虚拟机的服务级别目标 (SLO) 发生更改时。
- 计划修复已经持续了很长时间的情况下。 查询存储提示最适合用于短期修复。
- 意外的性能回归。
广泛的潜在影响
无论参数集、源应用程序、用户或结果集如何，查询存储提示都会影响查询的所有执行。 对于意外的性能回归，可以使用 sys.sp_query_store_clear_hints 轻松移除使用 sys.sp_query_store_set_hints 创建的查询存储提示。
在生产环境中应用查询存储提示之前，仔细地对任务关键型系统或敏感系统的更改进行负载测试。
强制参数化和 RECOMPILE 提示不受支持
当数据库选项 PARAMETERIZATION 设置为 FORCED 时，不支持同时应用查询存储提示和 RECOMPILE 查询提示。 有关详细信息，请参阅强制参数化使用指南。
RECOMPILE 提示与数据库级别的强制参数化集不兼容。 如果数据库具有强制参数化集，并且 RECOMPILE 提示是查询存储中提示字符串集的一部分，则数据库引擎将忽略 RECOMPILE 提示，并将应用其他提示（如果已使用）。 此外，从 2022 年 7 月开始，在 Azure SQL 数据库中，应发出警告（错误代码 12461），指出 RECOMPILE 提示已被忽略。
有关可应用哪些查询提示的信息，请参阅支持的查询提示。
Query Store 使用方案
在需要跟踪工作负荷并确保其性能可预测的很多情况下，都可以使用 Query Store。 下面是可以考虑使用 Query Store 的一些示例：
找出并解决使用计划选择回归的查询
在常规查询执行过程中，查询优化器可以决定是否因下述重要输入变得不同而选择不同计划：数据基数已更改，索引已创建、更改或删除，统计信息已更新，等等。通常情况下，新计划要优于以前使用的计划，或二者的效果差不多。 但有时候，新计划的效果要差很多 - 这种情况称为计划选择更改回归。 在查询存储出现之前，这是一个很难确定和解决的问题，因为 SQL Server 没有针对使用过一段时间的执行计划为用户提供可供查看的内置数据存储。
使用查询存储，可快速执行以下操作：
- 确定你关注的时间段内（过去一小时、昨天、上周等）执行指标已降级的所有查询。 在 SQL Server Management Studio 中使用回归查询加快分析速度。
- 在回归查询中，很容易找到那些有多个计划的查询，以及由于计划选择错误而降级的查询。 使用“回归查询”中的“计划摘要”窗格来显示回归查询的所有计划及其在某个时间段的查询性能。
- 强制实施历史记录中的旧计划（如果该计划经证明效果更好）。 使用“回归查询”中的“强制计划”按钮，强制实施针对查询选择的计划。
有关方案的详细说明，请参阅 Query Store: A flight data recorder for your database （Query Store：数据库的网络流量数据记录器）博客。
确定和优化排名靠前的资源占用查询
虽然你的工作负荷可能会生成数千个查询，但通常情况下，使用大部分系统资源的实际上只是其中一部分查询，因此你只需要注意这部分查询。 通常情况下，在资源使用排名靠前的查询中，你会发现有些查询是回归性查询，有些查询则可在进一步优化后得到改善。
开始浏览时，最便捷的方式是打开 Management Studio 中“资源使用排名靠前的查询”。 用户界面分成三个窗格：一个直方图，代表资源使用排名靠前的查询（左）；一个针对所选查询的计划摘要（右）；一个针对所选计划的可视化查询计划（底部）。 选择“配置”来控制要分析的查询数量和要设置的时间间隔。 此外，还可以在不同的资源消耗维度（持续时间、CPU、内存、IO、执行数）和基线（平均、最小、最大、总计、标准偏差）之间进行选择。
查看右侧的计划摘要，以便分析执行历史记录并了解各种不同的计划及其运行时统计信息。 使用底部窗格检查各种不同的计划，或者用肉眼对这些并排呈现的计划进行比较（使用“比较”按钮）。
如果确定某个查询的性能不够理想，则可根据问题性质进行操作：
- 如果所执行的查询具有多个计划，而最后一个计划明显不如前面的计划，则可通过计划强制机制来确保 SQL Server 在今后执行查询时使用最佳计划
- 查看优化器是否建议了 XML 计划中缺失的索引。 如果答案为是，则请创建该缺失的索引，并在创建完索引后使用 Query Store 来评估查询性能。
- 确保查询使用的基础表的统计信息是最新的。
- 确保查询所使用的索引已进行碎片整理。
- 考虑重新编写成本高的查询。 例如，可以充分利用查询参数化，减少动态 SQL 的使用。 在读取数据时实施最佳逻辑（在数据库端而非应用程序端应用数据筛选）。
A/B 测试
使用查询存储来比较更改应用程序前后的工作负载性能。
在下表包含的多个示例中，你可以使用 Query Store 来评估环境或应用程序更改对工作负荷性能的影响：
任何此类方案都可应用以下工作流：
- 在进行计划的更改之前，使用 Query Store 运行工作负荷，以便生成性能基线。
- 在控制的时间点应用应用程序更改。
- 继续运行工作负荷，直至生成更改后的系统性能图。
- 对 #1 和 #3 的结果进行比较。
- 打开“数据库总体使用情况”以确定对整个数据库的影响。
- 打开“资源使用排名靠前的查询”（或使用 Transact-SQL 运行自己的分析），以便分析所做的更改对最重要查询的影响。
- 决定是保留所做的更改，还是在无法接受新性能的情况下进行回退。
下图显示了如何在创建缺失索引的情况下进行 Query Store 分析（步骤 4）。 打开“资源使用排名靠前的查询”/“计划摘要”窗格，此时将显示会受索引创建操作影响的查询的该视图：
此外，你还可以在索引创建前后对计划进行比较，只需将这些计划并排呈现即可。 （“在单独的窗口中比较选定查询的计划”工具栏选项，此选项已在工具栏中使用红色正方形进行标记。）
在创建索引之前的计划（plan_id = 1，见上）提示索引缺失，你可以通过检查发现 Clustered Index Scan 是查询中成本最高的运算符（红色矩形）。
在创建缺失索引之后的计划（plan_id = 15，见下）现在可以使用 Index Seek (Nonclustered) 来减少查询的总体成本并改进其性能（绿色矩形）。
根据分析，查询性能获得了提升，因此你会保留索引。
在升级到新版 SQL Server 期间保持性能稳定
在 SQL Server 2014 (12.x) 之前，用户在升级到最新的平台版本时要冒性能下降的风险。 之所以会出现这种情况，是因为最新版查询优化器会在新版本安装之后即时启用。
自 SQL Server 2014 (12.x) 起，所有查询优化器更改都会绑定到最新的数据库兼容性级别，因此计划不会在升级后立即更改，而是在用户将 COMPATIBILITY_LEVEL 数据库更改为最新版本后更改。 利用此功能和 Query Store，你可以在升级过程中对查询性能进行精确的控制。 建议的升级工作流如下图所示：
升级 SQL Server 而不更改数据库兼容性级别。 它不会公开最新的查询优化器更改，但仍会提供包括查询存储在内的新版 SQL Server 功能。
启用“查询存储”。 有关详细信息，请参阅使查询存储适应工作负荷。
允许查询存储捕获查询和计划，并建立包含源/以前的数据库兼容性级别的性能基线。 在此步骤停留足够长的时间，确保捕获所有计划并获取稳定的基线。 这可以是生产工作负荷常用业务周期的持续时间。
转到最新数据库兼容性级别：向工作负载显示最新的查询优化器，以创建可能的新计划。
使用查询存储进行分析并解决回归问题：通常情况下，新查询优化器的改进会生成更好的计划。 不过，查询存储可以让你轻松识别计划选择回归并使用计划强制机制对其进行修复。 从 SQL Server 2017 (14.x) 开起，使用自动计划更正功能时，此步骤可自动进行。
a. 对于出现回归的情况，请在查询存储中强制执行之前已知的有效计划。
b. 如果存在未能强制执行的查询计划，或者如果性能仍不足，请考虑将数据库兼容级别还原到之前的设置，然后寻求 Microsoft 客户支持。
提示
使用 SQL Server Management Studio 升级数据库任务来升级数据库的数据库兼容性级别。 有关详细信息，请参阅使用查询优化助手升级数据库。
识别并改进临时工作负载
某些工作负载没有可通过优化来提高应用程序整体性能的主查询。 通常情况下，这些工作负荷的特点是有相对较大的不同查询，每个查询都会消耗一部分系统资源。 这些查询在性质上很独特，执行次数很少（通常仅执行一次，因此才称为即席查询），因此其运行时消耗并不重要。 另一方面，由于应用程序总是在生成全新的查询，因此大部分系统资源都消耗在没有进行优化的查询编译上。 这对于查询存储来说并不是理想情形，因为大量的查询和计划会占据你所保留的空间，这意味着查询存储可能很快就会进入只读模式。 如果你激活了“基于大小的清除策略”（强烈建议使用它来让 Query Store 始终处于启动和运行状态），则大部分时间会由后台进程清理 Query Store 结构，这也会消耗大量系统资源。
你可以通过“资源使用排名靠前的查询”视图，率先了解工作负载的即席性质：
可以通过“执行计数”度量值来分析排名靠前的查询是否为即席查询（这需要使用 QUERY_CAPTURE_MODE = ALL 运行 Query Store）。 从上图可以看出，90% 的“资源使用排名靠前的查询”仅执行一次。
此外，你可以通过运行 Transact-SQL 脚本来获取系统中查询文本、查询和计划的总数，并可通过比较 query_hash 和 query_plan_hash 来确定其差异：
SQL
| --Do cardinality analysis when suspect on ad hoc workloads SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text; SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query; SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM sys.query_store_query; SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan; SELECT COUNT(DISTINCT query_plan_hash) AS CountDifferentPlanRows FROM sys.query_store_plan; |
在工作负荷包含即席查询的情况下，你可能会获得这种结果：
查询结果显示，尽管查询存储中查询和计划的数量很大，其 query_hash 和 query_plan_hash 并没有什么不同。 唯一查询文本和唯一 query hash 的比率远远大于 1，这表明工作负荷适合进行参数化，因为这些查询之间的唯一差异就是作为查询文本一部分提供的文本常数（参数）。
通常，这种情况发生的条件是你的应用程序生成了查询（而不是调用存储过程或参数化查询），或者该应用程序依赖于会默认生成查询的对象关系映射框架。
如果你可以控制应用程序代码，则可以考虑重新编写数据访问层，以便利用存储过程或参数化查询。 不过，也可以在不更改应用程序的情况下显著改善这种状况，方法是针对整个数据库强制实施查询参数化（所有查询）或者使用同一 query_hash 针对单个查询模板进行操作。
使用单个查询模板进行操作时，需要创建计划指南：
SQL
| --Apply plan guide for the selected query template DECLARE @stmt nvarchar(max); DECLARE @params nvarchar(max); EXEC sp_get_query_template N'<your query text goes here>', @stmt OUTPUT, @params OUTPUT; EXEC sp_create_plan_guide N'TemplateGuide1', @stmt, N'TEMPLATE', NULL, @params, N'OPTION (PARAMETERIZATION FORCED)'; |
使用计划指南的解决方案操作起来更精确，但需要完成更多的工作。
如果所有查询（或大部分查询）都可以候选进行自动参数化，则考虑为整个数据库配置 PARAMETERIZATION = FORCED。 有关详细信息，请参阅强制参数化使用指南。
SQL
| --Apply forced parameterization for entire database ALTER DATABASE <database name> SET PARAMETERIZATION FORCED; |
应用任何此类步骤之后，即可通过“资源使用排名靠前的查询”从另一个角度来了解你的工作负荷。
某些情况下，你的应用程序可能会生成大量不同的查询，而这些查询并不适合进行自动参数化。 在这种情况下，你会看到系统中存在大量查询，但唯一查询和唯一 query_hash 之间的比率可能接近 1。
在这种情况下，建议启用“针对即席工作负荷进行优化”服务器选项，防止将缓存内存浪费在不大可能再次执行的查询上。 若要防止在 Query Store 中捕获这些查询，可将 QUERY_CAPTURE_MODE 设置为 AUTO。
SQL
| EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1' GO RECONFIGURE WITH OVERRIDE GO ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR; ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO); |
sys.database_query_store_options
返回此数据库的查询存储选项。
适用于：SQL 数据库 SQL Server (SQL Server 2016 (13.x) 及更高版本) 。
展开表
| 列名称 | 数据类型 | 说明 |
|---|
| desired_state | smallint | 指示查询存储的所需操作模式，由用户显式设置。 0 = OFF 1 = READ_ONLY 2 = READ_WRITE 4 = READ_CAPTURE_SECONDARY |
| desired_state_desc | nvarchar(60) | 查询存储所需操作模式的文本说明： OFF READ_ONLY READ_WRITE READ_CAPTURE_SECONDARY |
| actual_state | smallint | 指示查询存储的操作模式。 除了用户所需的所需状态列表外，实际状态可以是错误状态。 0 = OFF 1 = READ_ONLY 2 = READ_WRITE 3 = 错误 4 = READ_CAPTURE_SECONDARY |
| actual_state_desc | nvarchar(60) | 查询存储的实际操作模式的文本说明。 OFF READ_ONLY READ_WRITE ERROR READ_CAPTURE_SECONDARY 在某些情况下，实际状态与所需状态不同： - 如果数据库设置为只读模式或查询存储大小超出其配置的配额，即使用户指定了读写，查询存储也可以在只读模式下运行。 - 在极端情况下，查询存储可能会由于内部错误而进入错误状态。 从 SQL Server 2017 (14.x) 开始，如果出现这种情况，可通过在受影响的数据库内执行 sp_query_store_consistency_check 存储过程来恢复查询存储。 如果运行sp_query_store_consistency_check不起作用，或者使用的是 SQL Server 2016 (13.x) ，则需要通过运行 来清除数据ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR ALL; |
| readonly_reason | int | 当desired_state_desc READ_WRITE且actual_state_desc READ_ONLY时，readonly_reason返回位映射，以指示查询存储处于只读模式的原因。 1 - 数据库处于只读模式 2 - 数据库处于单用户模式 4 - 数据库处于紧急模式 8 - 数据库是辅助副本 (replica) (适用于可用性组和Azure SQL数据库异地复制) 。 只能在 可读 次要副本上有效地观察此值 65536 - 查询存储已达到 选项设置MAX_STORAGE_SIZE_MB的大小限制。 有关此选项的详细信息，请参阅 alter DATABASE SET options (Transact-SQL) 。 131072 - 查询存储中的不同语句数已达到内部内存限制。 请考虑删除不需要的查询或升级到更高的服务层级，以便将查询存储传输到读写模式。 262144 - 等待保留在磁盘上的内存中项的大小已达到内部内存限制。 查询存储将暂时处于只读模式，直到内存中项保留在磁盘上。 524288 - 数据库已达到磁盘大小限制。 查询存储是用户数据库的一部分，因此，如果数据库没有更多可用空间，则意味着查询存储不能再进一步增长。 若要将查询存储操作模式切换回读写模式，请参阅使用查询存储的最佳做法中的验证查询存储是否持续收集查询数据部分。 |
| current_storage_size_mb | bigint | 磁盘上查询存储的大小（以 MB 为单位）。 |
| flush_interval_seconds | bigint | 查询存储数据定期刷新到磁盘的时间段（以秒为单位）。 默认值为 900 (15 分钟) 。 使用 语句进行更改 ALTER DATABASE <database> SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = <interval>) 。 |
| interval_length_minutes | bigint | 统计信息聚合间隔（以分钟为单位）。 不允许使用任意值。 使用以下方法之一：1、5、10、15、30、60 和 1440 分钟。 默认值为 60 分钟。 |
| max_storage_size_mb | bigint | 查询存储的最大磁盘大小，以 MB (MB) 为单位。 默认值为 100 MB（截至 2017 SQL Server 2017 (14.x) ），从 SQL Server 2019 开始为 1 GB， (15.x) 。 对于 SQL 数据库 高级版，默认值为 1 GB，对于 SQL 数据库 基本版，默认值为 10 MB 。 使用 语句进行更改 ALTER DATABASE <database> SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <size>) 。 |
| stale_query_threshold_days | bigint | 查询信息保留在查询存储中的天数。 默认值为 30。 设置为 0 可禁用保留策略。 对于 SQL 数据库 基本版，默认值为 7 天。 使用 语句进行更改 ALTER DATABASE <database> SET QUERY_STORE ( CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = <value> ) ) 。 |
| max_plans_per_query | bigint | 限制存储计划的最大数目。 默认值为 200。 如果达到最大值，查询存储停止捕获该查询的新计划。 将 设置为 0 将消除与捕获的计划数相关的限制。 使用 语句进行更改 ALTER DATABASE<database> SET QUERY_STORE (MAX_PLANS_PER_QUERY = <n>) 。 |
| query_capture_mode | smallint | 当前活动查询捕获模式： 1 = ALL - 捕获所有查询。 这是 SQL Server (SQL Server 2016 (13.x) 及更高版本) 的默认配置值。 2 = AUTO - 根据执行计数和资源消耗捕获相关查询。 这是SQL 数据库的默认配置值。 3 = NONE - 停止捕获新查询。 查询存储将继续为已经捕获的查询收集编译和运行时统计信息。 请谨慎使用此配置，因为可能会错过重要查询的捕获。 4 = CUSTOM - 允许使用 QUERY_CAPTURE_POLICY选项对查询捕获策略进行额外控制。 适用于：SQL Server 2019 (15.x) 及更高版本。 |
| query_capture_mode_desc | nvarchar(60) | 查询存储的实际捕获模式的文本说明： SQL Server 2016 (13.x) ) 的所有 (默认值 SQL 数据库) 的 AUTO (默认值 无 CUSTOM |
| capture_policy_execution_count | int | 查询捕获模式自定义策略选项。 定义在评估期间执行查询的次数。 默认值为 30。 适用于：SQL Server 2019 (15.x) 及更高版本。 |
| capture_policy_total_compile_cpu_time_ms | bigint | 查询捕获模式自定义策略选项。 定义查询在评估期间使用的总编译 CPU 时间。 默认值为 1000。 适用于：SQL Server 2019 (15.x) 及更高版本。 |
| capture_policy_total_execution_cpu_time_ms | bigint | 查询捕获模式自定义策略选项。 定义查询在评估期间使用的总执行 CPU 时间。 默认值为 100。 适用于：SQL Server 2019 (15.x) 及更高版本。 |
| capture_policy_stale_threshold_hours | int | 查询捕获模式自定义策略选项。 定义评估间隔时段以确定是否应捕获查询。 默认值为 24 小时。 适用于：SQL Server 2019 (15.x) 及更高版本。 |
| size_based_cleanup_mode | smallint | 控制当数据总量接近最大大小时是否自动激活清除： 0 = OFF - 不会自动激活基于大小的清理。 1 = AUTO - 当磁盘大小达到max_storage_size_mb的 90% 时，将自动激活基于 大小的清理。 这是默认的配置值。 基于大小的清除首先会删除成本最低和最旧的查询。 当达到大约 80% 的 max_storage_size_mb 时，它会停止。 |
| size_based_cleanup_mode_desc | nvarchar(60) | 查询存储的实际基于大小的清理模式的文本说明： OFF AUTO (默认) |
| wait_stats_capture_mode | smallint | 控制查询存储是否捕获等待统计信息： 0 = OFF 1 = ON 适用于：SQL Server 2017 (14.x) 及更高版本。 |
| wait_stats_capture_mode_desc | nvarchar(60) | 实际等待统计信息捕获模式的文本说明： OFF ON (默认) 适用于：SQL Server 2017 (14.x) 及更高版本。 |
| actual_state_additional_info | nvarchar (8000) | 当前未使用。 |
总结
1、从 SQL Server 2022 (16.x) 开始，默认为所有新创建的数据库启用查询存储。
2、每个月占用空间大概5GB左右
3、“查询存储”的数据存储在本身自己的数据库文件中
SQL总结
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 | ALTER DATABASE [数据库名称] SET QUERY_STORE = ON GO ALTER DATABASE [数据库名称] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 10240, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO) GO -- 当前占用大小 current_storage_size_mb SELECT * FROM sys.database_query_store_options; select name, 'ALTER DATABASE ['+d.name++'] SET QUERY_STORE (OPERATION_MODE = READ_WRITE,CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),DATA_FLUSH_INTERVAL_SECONDS = 900,INTERVAL_LENGTH_MINUTES = 60,MAX_STORAGE_SIZE_MB = 10240,QUERY_CAPTURE_MODE = AUTO,SIZE_BASED_CLEANUP_MODE = AUTO);' QUERY_STORE, 'ALTER DATABASE ['+d.name++'] SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); ' AUTOMATIC_TUNING from sys.databases d where d.name not in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') and is_query_store_on=0; SELECT TOP 20 qsq.query_id, SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads, SUM(qrs.count_executions) AS sum_executions, AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads, SUM(qsq.count_compiles) AS sum_compiles, (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text, TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2 WHERE qsp2.query_id=qsq.query_id ORDER BY qsp2.plan_id DESC)) AS query_plan FROM sys.query_store_query qsq WITH(NOLOCK) JOIN sys.query_store_plan qsp WITH(NOLOCK) on qsq.query_id=qsp.query_id CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id JOIN sys.query_store_runtime_stats_interval qsrsi WITH(NOLOCK) on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id WHERE qsp.query_plan like N'%<MissingIndexes>%' and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME()) GROUP BY qsq.query_id, qsq.query_hash ORDER BY est_logical_reads DESC; GO -- 通过查询存储获取性能问题的SQL(执行时间、cpu、IO、tempdb、内存、日志等) SELECT TOP 50 qsq.query_id, qsq.object_id, object_name(qsq.object_id) ob_name, SUM(qrs.count_executions) AS sum_executions, SUM(qsq.count_compiles) AS sum_compiles, sum(qrs.avg_duration)/1000000 AS sum_duration_s, avg(qrs.avg_duration)/1000000 AS avg_duration_s, (CASE WHEN CEILING(sum(qrs.avg_duration)/1000000) < 36000 THEN '0' ELSE '' END + RTRIM((cast(FLOOR(sum(qrs.avg_duration)/1000000/3600) as bigint))) + ':' + RIGHT('0' + RTRIM((cast(FLOOR(sum(qrs.avg_duration)/1000000) as bigint))%3600/60), 2) + ':' + RIGHT('0' + RTRIM((cast(CEILING(sum(qrs.avg_duration)/1000000) as bigint))%60), 2)) sum_duration_s2, (CASE WHEN CEILING(avg(qrs.avg_duration)/1000000) < 36000 THEN '0' ELSE '' END + RTRIM((cast(FLOOR(avg(qrs.avg_duration)/1000000/3600) as bigint))) + ':' + RIGHT('0' + RTRIM((cast(FLOOR(avg(qrs.avg_duration)/1000000) as bigint))%3600/60), 2) + ':' + RIGHT('0' + RTRIM((cast(CEILING(avg(qrs.avg_duration)/1000000) as bigint))%60), 2)) avg_duration_s2, AVG(qrs.avg_logical_io_reads) AS avg_logical_io_reads, AVG(qrs.avg_logical_io_writes) AS avg_logical_io_writes, AVG(qrs.avg_physical_io_reads) AS avg_physical_io_reads, AVG(qrs.avg_cpu_time) AS avg_cpu_time, AVG(qrs.avg_query_max_used_memory*8/1024) AS avg_query_max_used_memory_mb, AVG(qrs.avg_rowcount) AS avg_rowcount, AVG(qrs.avg_log_bytes_used/1024/1024) AS avg_log_bytes_used_mb, AVG(qrs.avg_tempdb_space_used*8/1024) AS avg_tempdb_space_used_mb, -- >= mssql 2017 count(distinct qsp.plan_id) query_plan_count, CAST(DATEADD(HH, +8, qsq.last_execution_time) AS DATETIME) last_execution_time, qrs.execution_type_desc, (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text, TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2 WHERE qsp2.query_id=qsq.query_id ORDER BY qsp2.plan_id DESC)) AS query_plan FROM sys.query_store_query qsq WITH(NOLOCK) JOIN sys.query_store_plan qsp WITH(NOLOCK) on qsq.query_id=qsp.query_id JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id JOIN sys.query_store_runtime_stats_interval qsrsi WITH(NOLOCK) on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id WHERE 1=1 -- and qsrsi.start_time >= DATEADD(HH, -24, SYSDATETIME()) and DATEADD(HH, +8, qsrsi.start_time) >='2024-05-14 18:00:00' and DATEADD(HH, +8, qsrsi.end_time) <='2024-05-15 09:00:00' and DATEADD(HH, +8, qsq.last_execution_time) between '2024-05-14 18:00:00' and '2024-05-15 09:00:00' and avg_duration>60*1000*1000 -- 60s --and qsq.object_id IN (OBJECT_ID('US_KSRB') ,OBJECT_ID('USPXGYB') ) -- and qsq.query_id=36284 GROUP BY qsq.query_id, qsq.object_id,qsq.last_execution_time,qrs.execution_type_desc ORDER BY avg_duration_s DESC; GO -- select * from sys.query_store_query qsq where qsq.query_id=8389; -- select * from sys.query_store_plan qsp where qsp.query_id=8389; -- select * from sys.query_store_runtime_stats a where a.plan_id in (6616,6972); -- select * from sys.query_store_runtime_stats_interval where runtime_stats_interval_id in(19,61) -- select * from sys.query_store_wait_stats a where a.plan_id in (00,6805) order by a.total_query_wait_time_ms desc; -- @version>=2017 -- select CAST(DATEADD(HH, +8, a.start_time) AS DATETIME) start_time, -- CAST(DATEADD(HH, +8, a.end_time) AS DATETIME) end_time, -- a.runtime_stats_interval_id -- from sys.query_store_runtime_stats_interval a |
所有库的查询重写配置查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | /***********************************************************/ /***************************** 查询重写配置 **********************/ /***********************************************************/ DECLARE @database_name VARCHAR(50) DECLARE @SQL_STR VARCHAR(2000) IF OBJECT_ID('tempdb..#database_query_store_options') IS NOT NULL DROP TABLE #database_query_store_options CREATE TABLE #database_query_store_options(db_name nvarchar(200),desired_state smallint, desired_state_desc nvarchar(120) , actual_state smallint, actual_state_desc nvarchar(120), readonly_reason int , current_storage_size_mb bigint , flush_interval_seconds bigint , interval_length_minutes bigint , max_storage_size_mb bigint, stale_query_threshold_days bigint, max_plans_per_query bigint, query_capture_mode smallint , query_capture_mode_desc nvarchar(120) , size_based_cleanup_mode smallint , size_based_cleanup_mode_desc nvarchar(120), wait_stats_capture_mode smallint, wait_stats_capture_mode_desc nvarchar(120), actual_state_additional_info nvarchar(max)) DECLARE DATEBASE_INFO_CURSOR CURSOR FOR SELECT name FROM sys.databases -- where state=0 where name not in ('master','model','msdb','tempdb') and state=0 ORDER BY Name OPEN DATEBASE_INFO_CURSOR FETCH NEXT FROM DATEBASE_INFO_CURSOR INTO @database_name WHILE @@FETCH_STATUS=0 BEGIN SET @SQL_STR='INSERT INTO #database_query_store_options SELECT '''+@database_name+''', desired_state, desired_state_desc, actual_state, actual_state_desc, readonly_reason, current_storage_size_mb, flush_interval_seconds, interval_length_minutes, max_storage_size_mb, stale_query_threshold_days, max_plans_per_query, query_capture_mode, query_capture_mode_desc, size_based_cleanup_mode, size_based_cleanup_mode_desc, wait_stats_capture_mode, wait_stats_capture_mode_desc, actual_state_additional_info FROM ['+@database_name+'].sys.database_query_store_options ' print (@SQL_STR) EXEC (@SQL_STR) FETCH NEXT FROM DATEBASE_INFO_CURSOR INTO @database_name END CLOSE DATEBASE_INFO_CURSOR DEALLOCATE DATEBASE_INFO_CURSOR |
参考
https://learn.microsoft.com/zh-cn/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/relational-databases/performance/query-store-usage-scenarios?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/relational-databases/performance/manage-the-query-store?view=sql-server-ver16&tabs=ssms
https://learn.microsoft.com/zh-cn/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/relational-databases/performance/manage-the-query-store?view=sql-server-ver16&tabs=ssms
https://learn.microsoft.com/zh-cn/sql/relational-databases/performance/query-store-hints?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-database-query-store-options-transact-sql?view=sql-server-ver16
https://blog.csdn.net/weixin_38623994/article/details/104585134
https://learn.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-database-query-store-options-transact-sql?view=sql-server-ver16
查询存储函数
此函数有助于执行查询存储操作。
sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)
查询存储目录视图
目录视图提供了查询存储的相关信息。
sys.database_query_store_options (Transact-SQL)
sys.query_context_settings (Transact-SQL)
sys.query_store_plan (Transact-SQL)
sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-SQL)
sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)
sys.query_store_runtime_stats_interval (Transact-SQL)
sys.database_query_store_internal_state (Transact-SQL)
查询存储的存储过程
存储过程配置了查询存储。
sp_query_store_flush_db (Transact-SQL)
sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_force_plan (Transact-SQL)
sp_query_store_unforce_plan (Transact-SQL)
sp_query_store_remove_plan (Transact-SQL)
sp_query_store_remove_query (Transact-SQL)
sp_query_store_clear_message_queues (Transact-SQL)
sp_query_store_consistency_check (Transact-SQL)1
1 在极端情况下，查询存储可能由于内部错误而进入 ERROR 状态。 从 SQL Server 2017 (14.x) 开始，如果出现这种情况，可通过在受影响的数据库内执行 sp_query_store_consistency_check 存储过程来恢复查询存储。 请参阅 sys.database_query_store_options，了解 actual_state_desc 列说明中所述的详细信息。
