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”時，我們會在消息頁籤中看到以下資訊：

表 '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 是個不錯的選擇。