[SQL Server] How to use Query Store check SP's plan detail

2022-12-01

315
0
Maintenance
2022-12-07

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:

SQL Server

回首頁

當Eva搭上DBA航班之後

只要你還在嘗試，就還不算失敗。