SQLServer統計監控SQL執行計劃突變的方法

Eric zhou發表於2024-05-13

使用動態管理檢視(DMVs)來檢測SQL執行計劃的突變,你需要關注那些能夠提供查詢執行統計和計劃資訊的檢視。以下是一些可以用於此目的的DMVs以及相應的查詢示例:

  1. sys.dm_exec_query_stats:這個檢視提供了關於SQL Server中查詢執行的統計資訊,包括CPU時間、總工作時間、執行次數等。
SELECT sql_handle, statement_start_offset, statement_end_offset, creation_time, last_execution_time, execution_count, total_worker_time, total_elapsed_time FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC;
  1. sys.dm_exec_sql_text:結合sys.dm_exec_query_stats使用,可以獲取與sql_handle對應的SQL文字。
SELECT qs.sql_handle, st.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st;
  1. sys.dm_exec_cached_plans:這個檢視包含了快取中的執行計劃的統計資訊,如計劃的大小、執行次數等。
SELECT cacheobjtype, usecounts, cacheobjtype, objtype, size_in_bytes, creation_time, last_use_time FROM sys.dm_exec_cached_plans ORDER BY usecounts DESC;
  1. sys.dm_exec_query_plan:這個檢視與sys.dm_exec_sql_text結合使用,可以獲取特定查詢的執行計劃。
SELECT qp.query_plan, st.text FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;

要檢測執行計劃的突變,可以定期執行上述查詢,並將結果儲存在表中,或者與之前的結果進行比較。

例如,可以比較兩個不同時間點的total_worker_timetotal_elapsed_time,以檢視是否有顯著變化,這可能表明執行計劃已經改變。

由於sys.dm_exec_query_stats中的資訊可能會被SQL Server週期性地重置,因此最好的做法是將這些資訊記錄到一個持久化表中,這樣可以基於歷史資料來檢測突變。

此外,執行計劃的突變可能需要結合多個DMVs的資訊來綜合判斷。

相關文章