Query Store可以很方便的查詢SP在每一次執行所使用的Plan
但是要特別注意!!!!
在特別繁忙的SQL Server或是特定版本的SQL Server
在使用Query Store功能時有可能會使SQL Server發生memory dump的問題…
KB4480644 - FIX: A dump file may be generated when you run the DML internal plan on Query Store enabled database in SQL Server 2017
操作步驟與介紹
Step 1 : 需要先有DB name & SP name
Step 2 : 透過下面的Script找出Query_ID
USE [DB name]
go
SELECT distinct
p1.query_id
,object_name(object_id) AS [Stored Procedure]
FROM sys.query_store_query_text AS qt with(nolock)
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
WHERE object_name(object_id) = 'SP name'
Step 3 : 輸入Query_ID查看此SP最近一天內執行的狀況
在DB下面展開Query Store → Tracked Queries → View Tracked Queries
輸入ID按下旁邊綠色三角形的執行鈕, 會出現最近一天跑的紀錄
- Y軸是平均執行時間(毫秒ms)
- X軸是執行時間點
- 圓形 : 執行完成;正方形 : 已取消;三角形 : 已失敗。
- 圓形的大小是表示執行次數, 執行越多圓形會越大
- 不同顏色代表不同的執行計畫
- 如果發現此SP的Query plan有好有壞, 可以點選你希望他使用的Plan後, 再點擊Force Plan去強制此SP使用該Plan, Force後該Plan就會出現黑色勾勾, 表示此SP目前都是使用這個Plan在執行, 可以用來暫時hotfix, 但不建議永久這樣
- 若想調整紀錄時間, 可點選右上角的Configure自行選擇時間區間
查詢SQL Server升級前後的效能應用
除了平常Tunning SP很方便以外, 升級SQL Server版本也能使用
可以在升級前先開啟Query Store, 放置一段時間後再升級SQL Server版本, 最後再開啟一次Query Store放置一段時間, 再使用以下語法去做比較
--範例: SQL Server升級前是version 2014, 升級後為2019
--若您的版本不同請在下面的where condition做修改
USE [DB name]
go
SELECT
object_name(object_id) AS [Stored Procedure]
, rs1.avg_duration as avg_duration_1
, p1.compatibility_level as compatibility_level_1
, rs2.avg_duration as avg_duration_2
, p2.compatibility_level as compatibility_level_2
into #temp
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_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 rsi1
ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
WHERE p1.compatibility_level = 150 -- SQL Server 2019
AND p2.compatibility_level = 120 -- SQL Server 2014
AND p1.plan_id <> p2.plan_id
AND object_name(object_id) is not null
select [Stored Procedure] ,avg(avg_duration_1)/1000000 as [2019AVSecond] , AVG(avg_duration_2)/1000000 as [2014AVSecond], (avg(avg_duration_1)-AVG(avg_duration_2))/1000000 AS [Difsecond]
from #temp
group by [Stored Procedure]
having avg(avg_duration_1)-AVG(avg_duration_2)>1000000 --升級前與升級後執行時間超過1S的SP, 可自行修改
order by [Difsecond] desc
Reference:
- https://learn.microsoft.com/zh-tw/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver16
- https://learn.microsoft.com/zh-tw/sql/relational-databases/performance/tune-performance-with-the-query-store?view=sql-server-ver16
- https://learn.microsoft.com/zh-tw/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16