如何找出你效能最差的SQL Server查詢

我經常會被反覆問到這樣的問題：”我有一個效能很差的SQL Server。我如何找出最差效能的查詢？“。因此在今天的文章裡會給你一些讓你很容易找到問題答案的資訊嚮導。

 

問SQL Server！

SQL Server的一個優點是它本身能回答幾乎所有你的問題，因為SQL Server在各個DMV和DMF裡儲存了很多故障排除資訊。另一方面這也是個缺點，因為你必須知道各個DMV/DMF，還有如何把它們解釋和關聯在一起。

至於你的最差效能SQL Server查詢的一個最重要的DMV是sys.dm_exec_query_stats。對於每個快取的執行計劃，SQL Server儲存了這個執行計劃在執行時的詳細資訊。另外SQL Server告訴你這個查詢消耗的CPU時間和I/O讀取。當我對效能很差的SQL Server進行故障排除時，這是我經常使用的基本DMV之一。

 

讓我們進入sys.dm_exec_query_stats！

當你對sys.dm_exec_query_stats進行一個簡單的SELECT查詢，你會得到有很多不同列的一個非常廣泛的記錄集——有大量的不同數字。

（檢視大圖）

我們來仔細看下它們。對於每個快取的執行計劃，SQL Server給你下列度量的資訊：

• Worker Time (columns …_工作者時間)
• Physical Reads (columns …_物理讀)
• Logical Writes (columns …_邏輯寫)
• Logical Reads (columns …_邏輯讀)
• SQLCLR Time (columns …_公共語言執行時間)
• Elapsed Time (columns …_執行時間)
• Row Count (columns …_行數)

對於每個度量，你得到4個集合資訊的不同列：

• 總值（Total value）
• 上個值（Last value）
• 最小值（Min value）
• 最大值（Max value）

手上有了這些資訊找出你效能最差的查詢是什麼。但首先你要知道什麼是你的效能瓶頸——CPU還是I/O限制？如果你的效能瓶頸是CPU限制，你可以用下列查詢問SQL Server根據CPU消耗列出前5個最差效能的查詢：

你可以看到這裡我使用了簡單的ORDER BY total_worker_time DESC來返回CPU密集的查詢。另外也通過呼叫sys.dm_exec_sql_text和sys.dm_exec_query_plan DMF來抓取SQL語句和執行計劃本身。下列程式碼顯示如何依據I/O消耗來找出你效能最差的查詢。

當在你面前有SQL語句和執行計劃時，你可以進一步分析查詢找出是什麼引起高CPU或I/O消耗。

 

小結

SQL Server是個驚豔的產品：它可以立即給你問題的很好答案。你只要知道在哪裡找你的答案。至於效能很差的查詢，你總應該通過分析DMV sys.dm_exec_query_stats開始，在這裡SQL Server儲存裡你執行計劃執行時統計資訊。

感謝關注！

 

參考文章：

https://www.sqlpassion.at/archive/2015/04/20/how-to-find-your-worst-performing-sql-server-queries/