使用查询存储优化性能
适用于：
SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics
SQL Server 查询存储功能提供在工作负载中发现和优化查询的功能，无论是通过 SQL Server Management Studio 可视化界面还是 T-SQL 查询。 本文详细介绍了如何获取可操作的信息来提高数据库中的查询性能，包括如何根据查询的使用情况统计信息和强制计划来标识查询。 还可以使用查询存储提示功能来识别查询并调整其查询计划，而无需更改应用程序代码。
- 有关如何收集此数据的详细信息，请参阅查询存储如何收集数据。
- 若要详细了解如何使用查询存储进行配置和管理，请参阅使用查询存储监视性能。
- 有关在 Azure SQL 数据库中运行查询存储的信息，请参阅 在 Azure 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;
具有多个计划的查询
这些查询特别有趣，因为计划选择更改可能造成它们的性能回归。 以下查询将这些查询和所有计划一同进行了标识：
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 小时内翻倍的所有查询。 此查询会并排比较所有运行时统计信息时间间隔：
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 的查询。 此度量的计算方式是最近平均执行和历史平均执行之差，再乘以最近执行数量。 它实际上表示相对于历史记录，引入了多少额外的持续时间最近执行：
--- "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，并指示优化器正常优化。
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 来取消强制执行为查询选定的计划。
EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;
另请参阅
- 使用查询存储监视性能
- Query Store 最佳实践
- 通过内存中 OLTP 使用查询存储
- Query Store 使用方案
- 查询存储的数据收集方式
- 查询存储存储过程 (Transact-SQL)
- 查询存储目录视图 (Transact-SQL)
- 打开活动监视器 (SQL Server Management Studio)
- 实时查询统计信息
- 活动监视器
- sys.database_query_store_options (Transact-SQL)
后续步骤
反馈
即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
提交和查看相关反馈