利用 sys.dm_exec_query_stats 尋找並最佳化SQL語句

今天在看Sql Server 2012 的新特性，當看到某一條時，居然發現了 sys.dm_exec_query_stats 系統檢視表進行了升級；又由於該試圖一直在用，並且相當的有用，可以說是尋找並最佳化Sql 語句的一大利器。所以，今天特做下記錄。

 

MSDN 上對  sys.dm_exec_query_stats 視圖的定義：返回 SQL Server 2012 中緩衝查詢計劃的彙總效能統計資訊。緩衝計劃中的每個查詢語句在該視圖中對應一行，並且行的生存期與計劃本身相關聯。在從緩衝刪除計劃時，也將從該視圖中刪除對應行。

其實說白了，該視圖存放的就是當前所有執行計畫的詳細資料，比如某條執行計畫共占CPU多少等等。因為該視圖對編譯次數、佔用CPU資源總量、執行次數等都進行了詳細的記錄，所以，可以說是最佳化 DB伺服器CPU 的一大利器。

 

由於該試圖是動態，所以並一定總是準確，也可能某條執行計畫在查詢的時間做了重編譯，得到了偏差的資訊等;另外，對於 sys.dm_exec_query_stats 中佔用資源最多的，並不一定是有效能問題的，要同時觀察執行次數 和 IO 讀寫等，而對於執行過於頻繁的，則要考慮在程式中加緩衝了;該系統試圖不能用作應急最佳化用，但是日常最佳化，一定要做一個重要的參考指標。

 

說了這麼久，下面放 最佳化的SQL ：

SELECT s2.dbid,     (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,       ( (CASE WHEN statement_end_offset = -1          THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)          ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,    execution_count,     plan_generation_num,     last_execution_time,       total_worker_time,     last_worker_time,     min_worker_time,     max_worker_time,    total_physical_reads,     last_physical_reads,     min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writesFROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  WHERE s2.objectid is null ORDER BY s1.total_worker_time desc

　　

具體列的含義，請參考文末。

 

接下來說下 Sql Server 2012 對  sys.dm_exec_query_stats  試圖的增強功能吧：添加了四列，以協助排除長時間啟動並執行查詢所存在的問題。可以使用 total_rows、min_rows、max_rows 和 last_rows 彙總行計數列，分隔那些從出現問題的查詢（可能缺少索引或查詢計劃出錯）中返回大量行的查詢。

具體意思，從名稱中就不難看出來；經過本人的試用之後，卻發現這個改進對於某些執行計畫並不是很實用，為什麼呢，因為執行計畫是可能接受參數的，所以行數的數量和參數密切相關，所以，對於返回行數和參數密切相關的執行計畫，這個改進沒有什麼用，反之，還是有一定參考作用的。

 

 sys.dm_exec_query_stats 的詳細說明：http://msdn.microsoft.com/zh-cn/library/ms189741(v=sql.110).aspx