SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查詢效能最佳化中的作用

標籤：style   color   io   使用   strong   sp   資料   on   問題   

近段時間以來，一直在探究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 次。

這裡面的一些資訊是非常重要的，另一部分則不是這樣。

掃描次數：在查詢中涉及到的表被訪問的次數。在我們的例子中，其中的表只被訪問了1次，由於查詢中不包括串連命令，這一資訊並不是十分有用，但如果查詢中包含有一個或多個串連，則這一資訊是十分有用的。

邏輯讀取：這是最有用的資料。我們知道，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查詢效能最佳化中的作用