SQL Server: 利用 SET STATISTICS IO 和 SET STATISTICS TIME 對T-SQL語句進行效能分析

我們可以用 SET STATISTICS IO 和 SET STATISTICS TIME 對T-SQL語句進行效能分析。

方法如下：

在SQL Server查詢分析器裡面，執行：

SET STATISTICS profile ONSET STATISTICS io ONSET STATISTICS time ONGO-- // The SQL statement to analysisGO

將“-- // The SQL statement to analysis”替換成我們要實際分析的SQL語句即可。

下面貼兩張來展示一下執行的結果：

下面我們來詳細看一下Messages：

SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.SQL Server parse and compile time:    CPU time = 218 ms, elapsed time = 223 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.(5 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblPanel'. Scan count 1, logical reads 78, physical reads 3, read-ahead reads 78, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblVersion'. Scan count 1, logical reads 26, physical reads 2, read-ahead reads 24, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblLayer'. Scan count 5, logical reads 25, physical reads 8, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblControls'. Scan count 5, logical reads 30, physical reads 13, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblButtonControl'. Scan count 5, logical reads 30, physical reads 10, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblButtonState'. Scan count 5, logical reads 30, physical reads 9, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblButtonCommand'. Scan count 1, logical reads 11383, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(22 row(s) affected)   CPU time = 125 ms,  elapsed time = 509 ms.

這些資訊對我們分析查詢的效能十分有用。

尤其是：

一、CPU執行時間

 SQL Server Execution Times:   CPU time = 125 ms,  elapsed time = 509 ms.

我們看到，elapsed time是一個相對比較大的值，因為資料庫此刻正忙，背後還可能在執行許多其他的任務，但CPU time則體現出了在當前我們分析的SQL語句中CPU所花費的時間。基本上對於相同的查詢、相同的資料庫，無論資料庫伺服器此刻忙碌與否，CPU time是一個相對比較穩定的時間，可以用來衡量SQL語句真實的CPU時間消耗。

二、I/O消耗

見以下資訊：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblPanel'. Scan count 1, logical reads 78, physical reads 3, read-ahead reads 78, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblVersion'. Scan count 1, logical reads 26, physical reads 2, read-ahead reads 24, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblLayer'. Scan count 5, logical reads 25, physical reads 8, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblControls'. Scan count 5, logical reads 30, physical reads 13, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblButtonControl'. Scan count 5, logical reads 30, physical reads 10, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblButtonState'. Scan count 5, logical reads 30, physical reads 9, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblButtonCommand'. Scan count 1, logical reads 11383, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(22 row(s) affected)

更詳細的解釋見這篇文章：

《sql查詢效能調試，用SET STATISTICS IO和SET STATISTICS TIME---解釋比較詳細》http://blog.csdn.net/leamonjxl/article/details/6639978