原文: SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查詢性能優化中的作用

近段時間以來，一直在探究SQL Server查詢性能的問題，當然也漫無目的的查找了很多資料，也從網上的大神們的文章中學到了很多，在這里，向各位大神致敬。正是受大神們無私奉獻精神的影響，所以小弟也作為回報，分享一下關于SET STATISTICS IO和SET STATISTICS TIME這兩條T_SQL命令，在查詢優化性能中的作用。

????? 首先我想說明一下這篇文章不是關于如何優化SQL Server查詢性能的，因為關于這方面的內容太多，太復雜。另外查看很多關于性能優化（該文章中，指的是查詢性能）的資料的過程中，發現幾乎所有都是用執行時間作為優化指標，但是用執行時間作為性能優劣的指標并不是那么合適。當然，我們優化查詢語句的最終目的就是減少查詢時間。

????? 引起 查詢時間不準確的原因，有以下兩個方面：

?????? 1.SQL Server會隨著服務器資源的變化，而進行自我調節。

???????因為我們通常測試的服務器和實際服務器的環境并不完全相同。例如，我們在一臺負載很重的服務器上進行反復的測試。你會發現每次的執行的時間，并不相同，當然差距并不大，但是這個差距足以讓我們的性能調節變得困難許多。當然你也可以反復執行求平均值，但是在負載很大的服務器上，你需要一種多么科學的標準來確定執行時間的平均值呢？

????? 2.SQL Server所要讀取的數據，有沒有在緩沖區中。

????? 因為SQL Server 每次讀取數據都必須從數據緩沖區中讀取，這個也叫邏輯讀。如果要讀的數據沒有在數據緩沖區中，就要從物理磁盤上讀取（物理讀）。

????? 以上兩個原因都會影響執行查詢語句所用的時間。

????? 說了那么多，那我們該用什么作為性能優化的標準呢？

????? 1.CPU的占用時間。

?????? 當數據庫執行查詢語句時，會用到很多服務器的資源。其中一種資源就是CPU的占用時間，如果數據庫沒有發生任何的改變，反復的運行同一個查詢，CPU的占用時間都是十分接近的。

?????? 2.IO操作的次數。

??????? IO操作的指標有很多，下面會比較詳細的描述。

?????? 通過上面的描述，我們知道，一個查詢所需要的CPU、IO資源越少，性能就會越好。如果我們按照這個標準來優化查詢，那么就會很容易的判斷出你的優化措施是降低了性能，還是提高了性能。想到了這，那么我們怎樣才能看到我的服務器資源使用情況呢？這個時候我們就想到了SET STATISTICS IO和SET STATISTICS TIME (之前的內容就算是我賣關子了啊，嘿嘿)

??????? SET STATISTIC IO和SET STATISTIC TIME像很多T_SQL語句那樣屬于開關命令（自己起得名字，就是用ON和OFF打開和關閉）。缺省狀態下是關閉的。接下來我們就開始使用這兩個命令了，好期待。

??????? 在這個例子中，我們使用之前建好的Test數據庫,使用Person表

??????? （一）首先我們使用SET STATISTICS TIME

???????? 1.首先，為了使每次的執行都在同一個起點上，我們使用下面的兩條命令，來清除SQL Server的數據和過程緩沖區，否則執行的查詢結果就沒有可比性了。???

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

????????? 2.執行SET STATISTIC TIME ON 打開CUP統計報表

???????? 這些準備工作完成后，我們可以執行下面查詢：

????????? select? * from Person where ID=50000

????????? 執行完上述命令之后，你可以在消息選項卡中得到下面信息

SQL Server 分析和編譯時間: ?? CPU 時間 = 0 毫秒，占用時間 = 20 毫秒。

SQL Server 分析和編譯時間: ?? CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。

(1 行受影響)

?SQL Server 執行時間: ?? CPU 時間 = 235 毫秒，占用時間 = 1508 毫秒。

下面我們來詳細的分析這些時間信息的含義。

?第一個“SQL Server 分析和編譯時間”指的是解析“select? * from Person where ID=50000”這條查詢語句，并將解析的結果放到過程緩沖區中，SQL Server使用的CPU運行時間和總的時間。

第二個“SQL Server 分析和編譯時間”指的是從過程緩沖區中取出解析結果，并且執行的時間。這個時間會很快。

如果接下來不清空緩沖區而直接運行“select? * from Person where ID=50000”，你會發現SQL Server 分析和編譯時間都為0，因為SQL Server這時，會直接使用緩沖區中的解析結果，因此就不需要編譯時間。

第三個“SQL Server執行時間”將會是我們最感興趣的時間，這個時間是執行這次查詢使用了多少CPU運行時間和運行查詢使用了多少時間。CPU運行時間是對運行查詢所需要的CPU資源的一種相對穩定的測量方法，與CPU的忙閑程度沒有關系。但是，每次運行查詢時這一數字也會有所不同，只是變化的范圍很小。總時間是對查詢執行所需要的時間（不計算阻塞或讀數據的時間），由于服務器上的負載是在不斷變化的，因此這一數據的變化范圍有時會相當地大。

?由于CPU占用時間是相對穩定的，所以你可以使用這一數據作為衡量你的優化措施是提高了查詢性能，還是降低了查詢性能。

（二）接下來我們使用SET STATISTICS IO

和上面的準備工作一樣，當我們執行完“select? * from Person where ID=50000”時，我們會在消息選項卡中看到以下信息：

(1 行受影響)
表 'Person'。掃描計數 5，邏輯讀取 10418 次，物理讀取 105 次，預讀 10418 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

這里面的一些信息是非常重要的，另一部分則可以不去考慮。

掃描次數：在查詢中涉及到的表被訪問的次數。在我們的例子中，Person表只被訪問了5次，由于查詢中不包括連接命令，這一信息并不是十分有用，但如果查詢中包含有一個或多個連接，則這一信息是十分有用的。

邏輯讀取：這是最有用的數據。我們知道，SQL Server在對任何數據進行操作前，必須先把數據讀取到數據緩沖區中。此外，我們也知道SQL Server何時會從數據緩沖區中讀取數據，并把數據讀取到大小為8k字節的頁中。邏輯讀取得意思就是指SQL Server為得到查詢結果，而必須從數據緩沖區中讀取的頁數。

SQL Server在執行邏輯讀的時候，不會讀取比實際結果多或者少的數據，因此在相同的數據集中，執行同一個查詢，得到的邏輯讀的結果總是相同的。因此，在進行查詢優化時邏輯讀的值就是來衡量你的優化措施是否可行的一個很好的標準。（在查詢時邏輯讀越少，其效率就越高，查詢速度就越快，反之，就慢）

物理讀取：在執行真正的查詢操作前，SQL Server必須從磁盤上向數據緩沖區中讀取他所需要的數據。在SQL Server開始執行查詢前，當它發現要讀的數據不再數據緩沖區中時，它會首先把它需要的數據讀到數據緩沖區中。物理讀取的意思就是指SQL Server把所需數據讀到數據緩沖區中時，從物理磁盤上讀取的數據頁數。

遺憾的是，在我們進行查詢優化時，是不需要考慮物理讀的。盡管物理讀要比邏輯讀可能需要更多的服務器資源。因為SQL Server在執行查詢時，是不可能通過性能調節而減少物理讀的次數的。減少物理讀是一項很復雜并且重要的工作，它涉及到的是整個服務器的性能調節，而不僅僅是查詢性能的調節。在進行查詢性能調節時，我們是不能控制數據緩沖區大小或服務器的忙碌程度，以及完成查詢所需要的數據是在數據緩沖區還是在磁盤上，唯一我們可以控制的就是得到查詢結果多需要執行的邏輯讀的次數。因此在進行查詢優化時，我們大可不必在意物理讀的數據。

預讀：指的是SQL Server在進行查詢優化前，預測要讀取的數據頁，根據預讀的準確程度，預讀可能有用也可能沒用。和物理讀一樣，在我們進行查詢優化時是不需要考慮的。

剩下的幾個“lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次”意思和前面說的差不多，只是在進行增、刪、改的時候IO資源的操作情況。

所以呢，我們在做查詢優化的時候，使用SET STATISTICS TIME 和SET STATISTICS IO 是個不錯的選擇。????

?

SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查詢性能優化中的作用