MSSQL 利用 sys.dm_exec_query_stats 與 sys.dm_exec_sql_text 查詢 SQL查詢歷史與狀態 @ 黃昏的甘蔗

202206171718MSSQL 利用 sys.dm_exec_query_stats 與 sys.dm_exec_sql_text 查詢 SQL查詢歷史與狀態

如果沒有設定 Trigger 或特別的紀錄，怎麼查詢資料庫被執行過那些查詢或被動了什麼? 今天就來看看 sys.dm_exec_query_stats 與 sys.dm_exec_sql_text 可以做出那些有用的查詢

首先來看 sys.dm_exec_query_stats 可以查詢到哪些東西?

sql_handle 曾經執行過SQL紀錄的代碼，需要以此傳到 sys.dm_exec_sql_text
last_execution_time 上次執行的時間
execution_count 編譯完被執行的次數
total_rows 查詢傳回來的筆數
CPU : max_worker_time、讀取：max_physical_reads、寫入：max_logical_writes、執行時間：max_elapsed_time、
詳細請參考這裡 https://docs.microsoft.com/zh-tw/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver16

在官方的介紹範例裡

查詢最高5筆耗用CPU時間

SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text" FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash ORDER BY 2 DESC;

查詢執行次數排序

SELECT TOP 10 qs.execution_count, SUBSTRING(qt.text,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 ELSE qs.statement_end_offset end - qs.statement_start_offset )/2 ) AS query_text, qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.text like '%SELECT%' ORDER BY qs.execution_count DESC;

查詢一天內所執行過的SQL

SELECT QS.creation_time, ST.text
FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
where DateDiff(hh,QS.creation_time,getDate())<=24
ORDER BY QS.creation_time DESC

查詢被 Block 最多時間的查詢

SELECT TOP 10 CAST((QS.total_elapsed_time - QS.total_worker_time) /1000000.0 AS DECIMAL(16,2)) AS Block_time , QS.execution_count AS Exec_Count , ST.text as SQL_query
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE QS.total_elapsed_time > 0 AND ST.text NOT LIKE '%SCHEMA%'
ORDER BY Block_time DESC

Reference: