尋找資料庫訪問的效能瓶頸（適用於SQL Server 2005 及以上 ）

要對程式進行優化，首先要能找哪裡需要優化。

對於網站來說，資料庫訪問的效能是最先要考慮的因素。但是一個網站，每天都要進行成千上萬，甚至還要多得多次的資料庫查詢，怎麼找到應該優化哪條查詢語句呢？

需要考慮兩個因素：1）某條資料庫查詢語句的查詢頻率，2）以及每次查詢花費的時間。

如果一條查詢，即使比較慢，但是一天僅僅用到一兩次，那麼對它的優化也不是很迫切。另一條查詢，即使已經很快了，但是如果使用的頻率特別高，那麼哪怕幾毫秒，也是值得花力氣優化的。

在SQL Server中，為了在巨大數量的資料庫查詢中定位最需要優化的語句，有一個系統檢視 sys.dm_exec_query_stats，就會非常有用。

在SQL Server中進行下面這個查詢：

-- TOP 20  queries (by CPU)
SELECT TOP (20)
    [Total CPU (sec)] = total_worker_time * 0.000001,
    [Total Elapsed Time (sec)] = total_elapsed_time * 0.000001,
    [Execution Count] = execution_count,
    [Average CPU (sec)] = total_worker_time * 0.000001 / execution_count ,
    [DB Name] = DB_NAME(ST.dbid),
    [Object Name] = OBJECT_NAME(ST.objectid, ST.dbid),
    [Query Text] = SUBSTRING(ST.TEXT, (qs.statement_start_offset/2)+1,
                        ((CASE qs.statement_end_offset
                        WHEN -1 THEN DATALENGTH(ST.TEXT)
                        ELSE qs.statement_end_offset
                        END - qs.statement_start_offset)/2)+1),
    [Query Plan] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC 

可以得到下面這樣的結果，列出了花費 CPU 時間最多的查詢。具體來說，包括下面幾列：

• [Total CPU (sec)] 總 CPU 時間
• [Total Elapsed Time (sec)] 總實際時間
• [Execution Count] 查詢執行次數
• [Average CPU (sec)] 平均 CPU 時間
• [DB Name] 資料庫名稱
• [Object Name] 查詢物件名稱,
• [Query Text] 查詢的 SQL 文字
• [Query Plan] 查詢的執行計劃

這些SQL查詢，就是首先應該重點考慮優化的地方！！

這裡比較值得注意的有兩點

• CPU Time 和 Elapsed Time的區別：前者就是CPU進行計算的時間，後者是真正實際使用的時間，比包括其他一些等候的時間等等，但是 Elapsed Time 並不是總大於 CPU 時間，對於多核 CPU，CPU 時間常常大於 Elapsed Time，因為計算 CPU 時間的時候會把各個核各自花費的時間累加起來。
• 資料庫名稱：這個查詢時針對整個資料庫引擎例項的，因此裡面的各個資料庫的查詢都混在一起，遺憾的是，通過系統中的 dm_exec_sql_text 獲取資料庫ID的時候，對動態查詢無效，所以表裡只能返回 NULL。可以本文後面的參考文章。

基於上面的說明，我又做了一個更能體現效能指標的查詢：

SELECT execution_count, creation_time,
    [Execution/Hour] = execution_count * 3600.0 / DATEDIFF(s, creation_time, GETDATE()),
    [CPU/Execution] = total_worker_time * 0.000001 / execution_count,
    [CPU/Hour] = total_worker_time * 0.0036 / DATEDIFF(s, creation_time, GETDATE()), 
    [DB Name] = DB_NAME(ST.dbid),
    [Query Text] = SUBSTRING(ST.TEXT, (qs.statement_start_offset/2)+1,
                        ((CASE qs.statement_end_offset
                        WHEN -1 THEN DATALENGTH(ST.TEXT)
                        ELSE qs.statement_end_offset
                        END - qs.statement_start_offset)/2)+1),
    [Query Plan] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
WHERE execution_count * 3600.0 / DATEDIFF(s, creation_time, GETDATE()) > 1.0 
    and DATEDIFF(s, creation_time, GETDATE()) >3600
Order by [CPU/Hour] desc

這裡可以列出各個查詢語句的三個效能指標：

• 平均每小時中該查詢被執行的次數
• 平均每次執行該查詢花費的 CPU 時間（秒）
• 平均每小時中執行該查詢花費的總CPU時間（秒）

這三個指標去尋找執行的次數又多又慢的查詢，非常有效！第一個指標反映一個查詢的頻度，第二個指標反映這個查詢的速度，第三個等於前兩個相乘，反映考慮頻度權重的速度。

此外，在這個查詢中，對於平均每小時執行不了1次的查詢進行排除，以及剛剛編譯1小時以內的查詢也進行排除，避免資料干擾。

希望這篇文章對您有所幫助！

參考資料：

(total_elapsed_time < total_cpu_time) Vs. uses_parallelism
http://michaeljswart.com/2011/12/cxpacket-whats-that-and-whats-next

sys.dm_exec_query_stats (Transact-SQL)
http://msdn.microsoft.com/zh-cn/library/ms189741.aspx

sys.dm_exec_sql_text DBID column NULL for dynamic SQL
http://connect.microsoft.com/SQLServer/feedback/details/374600/sys-dm-exec-query-stats-dbid-column-null-for-dynamic-sql