T-SQL命令在SQL Server查詢中的運用

　　首先需要說明的是這篇文章的內容並不是如何調節SQL Server查詢效能的（有關這方面的內容能寫一本書），而是如何在SQL Server查詢效能的調節中利用SET STATISTICS IO和SET STATISTICS TIME這二條被經常忽略的Transact-SQL命令的。

　　從表面上看，查詢效能的調節是一件十分簡單的事。從本質上講，我們希望查詢的運行速度能夠儘可能地快，無論是將查詢啟動並執行時間從10分鐘縮減為1分鐘，還是將啟動並執行時間從2秒鐘縮短為1秒種，我們最終的目標都是減少啟動並執行時間。

　　儘管查詢效能調節困難的原因有許多，但這篇文章將只涉及其中的一個方面，其中最重要的原因是，每當使用環境發生變化時，就需要對效能進行調節，因此很難搞清楚到底需要如何調節查詢的效能。

　　如果象大多數使用者那樣在一台測試用的伺服器上進行效能調查，其效果往往並不是十分地令人滿意，因為測試伺服器的環境與實際應用的伺服器環境並不完全相同。隨著對資源要求的不斷變化，SQL Server會自動地進行自我調節。

　　如果對這一點有疑問，可以在一台負載很大的伺服器上反覆地運行同一個查詢，在大多數情況下，執行查詢所使用的時間並不相同。當然，差距並不大，但其變化足以使效能的調節比它應有的程度要困難一些。

　　這到底是怎麼回事兒？是你的想法錯了還是在執行查詢時，伺服器的負載過重？這是引起已耗用時間增加的原因嗎？儘管可以多次反覆地執行查詢得到一個平均時間，但這樣作的工作量很大。我們需要用一種很科學的標準對每次測試時的效能進行比較。

　　測量伺服器資源是解決查詢效能調節問題的關健

　　在伺服器上執行查詢時，會用到許多種伺服器資源。其中的一種資源是CPU的佔用時間，假設資料庫沒有發生任何改變，反覆地運行同一個查詢其CPU的佔用時間將是十分接近的。在這裡，我指的不是一個查詢從運行開始到結束的時間，而是指運行這一查詢所需要的CPU資源數量，運行一個查詢所需要的時間與伺服器的忙碌程度有關。

　　SQL Server需要的另一種資源是IO。無論何時執行查詢，SQL Server都必須從資料緩衝區中讀取資料（邏輯讀），如果所需要的資料沒有在緩衝區中，則需要到磁碟上讀取（物理讀）。

　　從討論中可以知道，一個查詢需要的CPU、IO資源越多，查詢啟動並執行速度就越慢，因此，描述查詢效能調節任務的另一種方式是，應該以一種使用更少的CPU、IO資源的方式重寫查詢命令，如果能夠以這樣一種方式完成查詢，查詢的效能就會有所提高。

　　如果調節查詢效能的目的是讓它使用儘可能少的伺服器資源，而不是查詢啟動並執行時間最短，那麼就更容易測試你採取的措施是提高了查詢的效能還是降低了查詢的效能。尤其是在資源利用不斷變化的伺服器上更是如此。首先，需要搞清楚在對查詢進行調節時，如何測試我們的伺服器的資源使用方式。

　　又想起了SET STATISTICS IO和SET STATISTICS TIME

　　SQL Server很早以前就支援SET STATISTICS IO和SET STATISTICS TIME這二條Transact-SQL命令了，但由於其他一些原因，在調節查詢的效能時，許多DBA（資料為系統管理員）都忽略了它們，也許是它們不大吸引人吧。但不管是什麼原因，我們下面就會發現，它們在調節查詢效能方面還是很有用的。

　　有三種方式可以使用這二條命令：使用Transact-SQL命令列方式、使用Query Analyzer、在Query Analyzer中設定當前串連適當的串連屬性。在這篇文章中，我們將使用Transact-SQL命令列的方式示範它們的用法。

　　SET STATISTICS IO和SET STATISTICS TIME的作用象開關那樣，可以開啟或關閉我們的查詢使用資源的各種報告資訊。預設狀態下，這些設定是關閉的。我們首先來看一個這些命令如何開啟的例子，並看看它們會報告一些什麼樣的資訊。

　　在開始我們的例子前，啟動Query Analyzer，並串連到一個SQL Server上。在本例中，我們將使用Northwind資料庫，並將它作為這個串連的預設資料庫。

　　然後，運行下面的查詢：

　　　SELECT * FROM [order details]

　　如果你沒有改動過order details這個表，這個查詢會返回2155個記錄。這是一個典型的結果，相信你已經在Query Analyzer中看到過好多次了。

　　現在我們來運行同一個查詢，不過這次在執行查詢之前，我們將首先運行SET STATISTICS IO和SET STATISTICS TIME命令。需要記住的是，這二個命令的開啟只對當前的串連有效，當開啟其中的一個或二個命令後，再關閉當前串連並開啟一個新的串連後，就需要再次執行相應的命令。如果想關閉當前串連中的這二個命令，只要將原來命令中的ON換成OFF，再執行一次就可以了。

　　在開始我們的例子前，先運行下面的這二條命令（不要在正在使用的伺服器上執行），這二條命令將清除SQL Server的資料和過程緩衝區，這樣能夠使我們在每次執行查詢時在同一個起點上，否則，每次執行查詢得到的結果就不具有可比性了：

　　　DBCC DROPCLEANBUFFERS

　　　DBCC FREEPROCCACHE

　　輸入並運行下面的Transact-SQL命令：

　　　SET STATISTICS IO ON

　　　SET STATISTICS TIME ON

　　一旦上面的準備工作完成後，運行下面的查詢：

　　　SELECT * FROM [order details]

　　如果同時運行上面所有的命令，你得到的輸出就會與我的不同，也就很難搞清楚到底發生了什麼事情。
在運行上述的命令後，就會在結果視窗中看到以前沒有看到過的新資料，在視窗的最頂端，會有下面的資訊：

SQL Server parse and compile time: （SQL Server解析和編譯時間：） CPU time = 10 ms, elapsed time = 61 ms. SQL Server parse and compile time: （SQL Server解析和編譯時間：） CPU time = 0 ms, elapsed time = 0 ms.

　　在顯示上面的資料後，查詢得到的記錄就會顯示出來。在顯示完2155條記錄後，會顯示出下面的資訊：

Table 'Order Details'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9. （表：Order Details，掃描次數 1，邏輯讀 10，物理讀 1，提前讀取 9） SQL Server Execution Times: （SQL Server執行時間：） CPU time = 30 ms, elapsed time = 387 ms.

　　（每次得到的結果可能各不相同，在下面我們討論顯示的資訊時會提到這一點。）

　　那麼，這些資訊的具體含意是什麼呢？下面我們就來詳細地進行分析。

　　　　SET STATISTICS TIME的結果

　　　　SET STATISTICS TIME命令用於測試各種操作的已耗用時間，其中一些可能對於查詢效能的調節沒有什麼用處。運行這一命令可以在螢幕上得到如下的顯示資訊：

　　輸出的最開始處：

SQL Server parse and compile time: CPU time = 10 ms, elapsed time = 61 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

　　輸出的結束處：

　　　SQL Server Execution Times:

　　　CPU time = 30 ms, elapsed time = 387 ms.

　　在輸出的最開始處我們可以看到二次測試時間，但第一行執行某一操作所需的CPU的時間和總共時間，但第二行似乎就不是了。

　　“SQL Server parse and compile time”表示SQL Server解析“ELECT * FROM [order details]”命令並將解析的結果放到SQL Server的過程緩衝區中供SQL Server使用所需要的CPU已耗用時間和總的時間。

　　在本例中，CPU的已耗用時間為10毫秒，總時間為61毫秒。由於伺服器的配置和負載不同，你得到的CPU已耗用時間、總時間這二個值可能會與本例中的測試結果有所不同。

　　第二行的“SQL Server parse and compile time”表示SQL Server從過程緩衝區中取出解析結果供執行的時間，大多數情況下這二個值都會是0，因為這個過程執行得相當地快。

　　如果不清除緩衝區而再次運行SELECT * FROM [order details]命令，CPU已耗用時間和編譯時間會都是0，因為SQL Server會重複使用緩衝區中的解析結果，因此就不需要再次編譯的時間了。

　　這些資訊在查詢效能的調節中對你的協助真的很大嗎？也許並非如此，但我將解釋一下這些資訊的真正含意，你將會很驚奇，大多數的DBA居然都不真正明白這些資訊的含意：

　　我們最感興趣的是顯示在輸出最後的時間資訊：

　　　SQL Server Execution Times:

　　　CPU time = 30 ms, elapsed time = 387 ms.

　　上面顯示的資訊表明，執行這次查詢使用了多少CPU已耗用時間和執行查詢使用了多少時間。CPU已耗用時間是對執行查詢所需要的CPU資源的一種相對穩定的測量方法，與CPU的忙閑程度沒有關係。但是，每次執行查詢時這一數字也會有所不同，只是變化的範圍沒有總時間變化大。總時間是對查詢執行所需要的時間（不計算阻塞或讀資料的時間），由於伺服器上的負載是在不斷變化的，因此這一資料的變化範圍有時會相當地大。

　　由於CPU佔用時間是相對穩定的，因此可以使用這一資料作為衡量你的調節措施是提高了查詢效能還是降低了查詢的效能的一種方法。

　　　SET STATISTICS IO的效果

　　　SET STATISTICS IO的輸出資訊顯示在輸出的結束處，下面是它顯示的一個例子：

　　　Table 'Order Details'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.

　　這些資訊中的一部分是十分有用的，另一部分則不然，我們來看看每個部分並瞭解其含意：

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

　　一個迴圈外部的表的Scan Count值為1，但對於一個迴圈內的表而言，其值為迴圈的次數。可以想象得到，對於一個迴圈內的表而言，其Scan Count值越小，它所使用的資源越少，查詢的效能也就越高。因此在調節一個帶串連的查詢的效能時，需要關注Scan Count的值，在進行調節時，注意觀察它是增加還是減少了。

　　Logical Reads: 這是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的資料。我們知道，SQL Server在可以對任何資料進行操作前，必須首先把資料讀取到其資料緩衝區中。此外，我們也知道SQL Server何時會從資料緩衝區中讀取資料，並把資料讀取到大小為8K位元組的頁中。

　　那麼Logical Reads的意義是什麼呢？Logical Reads是指SQL Server為得到查詢中的結果而必須從資料緩衝區讀取的頁數。在執行查詢時，SQL Server不會讀取比實際需求多或少的資料，因此，當在相同的資料集上執行同一個查詢，得到的Logical Reads的數字總是相同的。

　　為什麼說在調節查詢效能中知道SQL Server執行查詢時的Logical Reads值是很重要的呢？因為在每次執行同一查詢時，這個數值是不會變化的。因此，在進行查詢效能的調節時，這是一個可以用來衡量你的調節措施是否成功的一個很好的標準。

　　在對查詢的效能進行調節時，如果Logical Reads值下降，就表明查詢使用的伺服器資源減少，查詢的效能有所提高。如果Logical Reads值增加，則表示調節措施降低了查詢的效能。在其他條件不變的情況下，一個查詢使用的邏輯讀越少，其效率就越高，查詢的速度就越快。

　　Physical Reads：在這裡我要說的的東西可能初聽起來有點自相矛盾，但只要反覆思考，就會明白其中的真正含意。

　　物理讀指的是，在執行真正的查詢操作前，SQL Server必須從磁碟上向資料緩衝區中讀取它所需要的資料。在SQL Server開始執行查詢前，它要作的第一件事就是檢查它所需要的資料是否在資料緩衝區中，如果在，就從中讀取，如果不在，SQL Server必須首先將它需要的資料從磁碟上讀到資料緩衝區中。

　　我們可以想象得到，SQL Server在執行物理讀時比執行邏輯讀需要更多的伺服器資源。因此，在理想情況下，我們應當盡量避免物理讀操作。

　　下面的這一部分聽起來讓人容易感到糊塗了。在對查詢的效能進行調節時，可以忽略物理讀而只專註於邏輯讀。你一定會納悶兒，剛才不是還說物理讀比邏輯讀需要更多的伺服器資源嗎？

　　情況確實是這樣，SQL Server在執行查詢時所需要的物理讀次數不可能通過效能調節而減少的。減少物理讀的次數是DBA的一項重要工作，但它涉及到整個伺服器效能的調節，而不僅僅是查詢效能的調節。在進行查詢效能調節時，我們不能控制資料緩衝區的大小或伺服器的忙碌程度以及完成查詢所需要的資料是在資料緩衝區中還是在磁碟上，唯一我們能夠控制的資料是得到查詢結果所需要執行的邏輯讀的次數。

　　因此，在查詢效能的調節中，我們可以心安理得地不理會SET STATISTICS IO命令提供的Physical Read的值。（減少物理讀次數、加快SQL Server運行速度的一種方式是確保伺服器的實體記憶體足夠多。）

　　Read-Ahead Reads：與Physical Reads一樣，這個值在查詢效能調節中也沒有什麼使用者。Read-Ahead Reads表示SQL Server在執行預讀機制時讀取的物理頁。為了最佳化其效能，SQL Server在認為它需要資料之前預先讀取一部分資料，根據SQL Server對資料需求預測的準確程度，預讀的資料頁可能有用，也可能沒用。

　　在本例中，Read-Ahead Reads的值為9，Physical Read的值為1，而Logical Reads的值為10，它們之間存在著簡單的相加關係。那麼我在伺服器上執行查詢時的過程是怎麼樣的呢？首先，SQL Server會開始檢查完成查詢所需要的資料是否在資料緩衝區中，它會很快地發現這些資料不在資料緩衝區中，並啟動預讀機制將它所需要的10個資料頁中的前9個讀取到資料緩衝區。當SQL Server檢查是否所需要的全部資料都已經在資料緩衝區時，會發現已經有9個資料頁在資料緩衝區中，還有一個不在，它就會立即再次讀取磁碟，將所需要的頁讀到資料緩衝區。一旦所有的資料都在資料緩衝區後，SQL Server就可以處理查詢了。

　　我們應該怎麼辦？

　　我在本篇文章的開始曾提到，在對查詢的效能進行調節時用一些科學的標準來測量你的調節措施是否有效是十分重要的。問題是，SQL Servers的負載是動態變化的，使用查詢總的已耗用時間來衡量你正在調節效能的查詢的效能是提高了還是沒有，並不是一個合理的方法。

　　更好的方法是比較多個資料，例如邏輯讀的次數或者查詢所使用的CPU時間。因此在對查詢的效能進行調節時，需要首先使用SET STATISTICS IO和SET STATISTICS TIME命令向你提供一些必要的資料，以便確定你對查詢效能進行調節的措施是否真正地得到了目的。