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

Eric zhou發表於2024-05-13
SQLServer

使用動態管理檢視（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_time或total_elapsed_time，以檢視是否有顯著變化，這可能表明執行計劃已經改變。

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

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

相關文章