T-SQL命令在SQL Server查詢中的運(yùn)用
2024-08-31 00:48:14
供稿：網(wǎng)友
首先需要說明的是這篇文章的內(nèi)容并不是如何調(diào)節(jié)sql server查詢性能的（有關(guān)這方面的內(nèi)容能寫一本書），而是如何在sql server查詢性能的調(diào)節(jié)中利用set statistics io和set statistics time這二條被經(jīng)常忽略的transact-sql命令的。
從表面上看，查詢性能的調(diào)節(jié)是一件十分簡(jiǎn)單的事。從本質(zhì)上講，我們希望查詢的運(yùn)行速度能夠盡可能地快，無論是將查詢運(yùn)行的時(shí)間從10分鐘縮減為1分鐘，還是將運(yùn)行的時(shí)間從2秒鐘縮短為1秒種，我們最終的目標(biāo)都是減少運(yùn)行的時(shí)間。
盡管查詢性能調(diào)節(jié)困難的原因有許多，但這篇文章將只涉及其中的一個(gè)方面，其中最重要的原因是，每當(dāng)使用環(huán)境發(fā)生變化時(shí)，就需要對(duì)性能進(jìn)行調(diào)節(jié)，因此很難搞清楚到底需要如何調(diào)節(jié)查詢的性能。
如果象大多數(shù)用戶那樣在一臺(tái)測(cè)試用的服務(wù)器上進(jìn)行性能調(diào)查，其效果往往并不是十分地令人滿意，因?yàn)闇y(cè)試服務(wù)器的環(huán)境與實(shí)際應(yīng)用的服務(wù)器環(huán)境并不完全相同。隨著對(duì)資源要求的不斷變化，sql server會(huì)自動(dòng)地進(jìn)行自我調(diào)節(jié)。
如果對(duì)這一點(diǎn)有疑問，可以在一臺(tái)負(fù)載很大的服務(wù)器上反復(fù)地運(yùn)行同一個(gè)查詢，在大多數(shù)情況下，執(zhí)行查詢所使用的時(shí)間并不相同。當(dāng)然，差距并不大，但其變化足以使性能的調(diào)節(jié)比它應(yīng)有的程度要困難一些。
這到底是怎么回事兒？是你的想法錯(cuò)了還是在運(yùn)行查詢時(shí)，服務(wù)器的負(fù)載過重？這是引起運(yùn)行時(shí)間增加的原因嗎？盡管可以多次反復(fù)地運(yùn)行查詢得到一個(gè)平均時(shí)間，但這樣作的工作量很大。我們需要用一種很科學(xué)的標(biāo)準(zhǔn)對(duì)每次測(cè)試時(shí)的性能進(jìn)行比較。
測(cè)量服務(wù)器資源是解決查詢性能調(diào)節(jié)問題的關(guān)健
在服務(wù)器上執(zhí)行查詢時(shí)，會(huì)用到許多種服務(wù)器資源。其中的一種資源是cpu的占用時(shí)間，假設(shè)數(shù)據(jù)庫沒有發(fā)生任何改變，反復(fù)地運(yùn)行同一個(gè)查詢其cpu的占用時(shí)間將是十分接近的。在這里，我指的不是一個(gè)查詢從運(yùn)行開始到結(jié)束的時(shí)間，而是指運(yùn)行這一查詢所需要的cpu資源數(shù)量，運(yùn)行一個(gè)查詢所需要的時(shí)間與服務(wù)器的忙碌程度有關(guān)。
sql server需要的另一種資源是io。無論何時(shí)運(yùn)行查詢，sql server都必須從數(shù)據(jù)緩沖區(qū)中讀取數(shù)據(jù)（邏輯讀），如果所需要的數(shù)據(jù)沒有在緩沖區(qū)中，則需要到磁盤上讀取（物理讀）。
從討論中可以知道，一個(gè)查詢需要的cpu、io資源越多，查詢運(yùn)行的速度就越慢，因此，描述查詢性能調(diào)節(jié)任務(wù)的另一種方式是，應(yīng)該以一種使用更少的cpu、io資源的方式重寫查詢命令，如果能夠以這樣一種方式完成查詢，查詢的性能就會(huì)有所提高。
如果調(diào)節(jié)查詢性能的目的是讓它使用盡可能少的服務(wù)器資源，而不是查詢運(yùn)行的時(shí)間最短，那么就更容易測(cè)試你采取的措施是提高了查詢的性能還是降低了查詢的性能。尤其是在資源利用不斷變化的服務(wù)器上更是如此。首先，需要搞清楚在對(duì)查詢進(jìn)行調(diào)節(jié)時(shí)，如何測(cè)試我們的服務(wù)器的資源使用情況。
又想起了set statistics io和set statistics time
sql server很早以前就支持set statistics io和set statistics time這二條transact-sql命令了，但由于其他一些原因，在調(diào)節(jié)查詢的性能時(shí)，許多dba（數(shù)據(jù)為系統(tǒng)管理員）都忽略了它們，也許是它們不大吸引人吧。但不管是什么原因，我們下面就會(huì)發(fā)現(xiàn)，它們?cè)谡{(diào)節(jié)查詢性能方面還是很有用的。
有三種方式可以使用這二條命令：使用transact-sql命令行方式、使用query analyzer、在query analyzer中設(shè)置當(dāng)前連接適當(dāng)?shù)倪B接屬性。在這篇文章中，我們將使用transact-sql命令行的方式演示它們的用法。
set statistics io和set statistics time的作用象開關(guān)那樣，可以打開或關(guān)閉我們的查詢使用資源的各種報(bào)告信息。缺省狀態(tài)下，這些設(shè)置是關(guān)閉的。我們首先來看一個(gè)這些命令如何打開的例子，并看看它們會(huì)報(bào)告一些什么樣的信息。
在開始我們的例子前，啟動(dòng)query analyzer，并連接到一個(gè)sql server上。在本例中，我們將使用northwind數(shù)據(jù)庫，并將它作為這個(gè)連接的缺省數(shù)據(jù)庫。
然后，運(yùn)行下面的查詢：
select * from [order details]
如果你沒有改動(dòng)過order details這個(gè)表，這個(gè)查詢會(huì)返回2155個(gè)記錄。這是一個(gè)典型的結(jié)果，相信你已經(jīng)在query analyzer中看到過好多次了。
現(xiàn)在我們來運(yùn)行同一個(gè)查詢，不過這次在運(yùn)行查詢之前，我們將首先運(yùn)行set statistics io和set statistics time命令。需要記住的是，這二個(gè)命令的打開只對(duì)當(dāng)前的連接有效，當(dāng)打開其中的一個(gè)或二個(gè)命令后，再關(guān)閉當(dāng)前連接并打開一個(gè)新的連接后，就需要再次執(zhí)行相應(yīng)的命令。如果想關(guān)閉當(dāng)前連接中的這二個(gè)命令，只要將原來命令中的on換成off，再執(zhí)行一次就可以了。
在開始我們的例子前，先運(yùn)行下面的這二條命令（不要在正在使用的服務(wù)器上執(zhí)行），這二條命令將清除sql server的數(shù)據(jù)和過程緩沖區(qū)，這樣能夠使我們?cè)诿看螆?zhí)行查詢時(shí)在同一個(gè)起點(diǎn)上，否則，每次執(zhí)行查詢得到的結(jié)果就不具有可比性了：
dbcc dropcleanbuffers
dbcc freeproccache
輸入并運(yùn)行下面的transact-sql命令：
set statistics io on
set statistics time on
一旦上面的準(zhǔn)備工作完成后，運(yùn)行下面的查詢：
select * from [order details]
如果同時(shí)運(yùn)行上面所有的命令，你得到的輸出就會(huì)與我的不同，也就很難搞清楚到底發(fā)生了什么事情。
在運(yùn)行上述的命令后，就會(huì)在結(jié)果窗口中看到以前沒有看到過的新資料，在窗口的最頂端，會(huì)有下面的信息：
sql server parse and compile time: （sql server解析和編譯時(shí)間：）
cpu time = 10 ms, elapsed time = 61 ms.
sql server parse and compile time: （sql server解析和編譯時(shí)間：）
cpu time = 0 ms, elapsed time = 0 ms.
在顯示上面的數(shù)據(jù)后，查詢得到的記錄就會(huì)顯示出來。在顯示完2155條記錄后，會(huì)顯示出下面的信息：
table 'order details'. scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.
（表：order details，掃描次數(shù) 1，邏輯讀 10，物理讀 1，提前讀取 9）
sql server execution times:
（sql server執(zhí)行時(shí)間：）
cpu time = 30 ms, elapsed time = 387 ms.
（每次得到的結(jié)果可能各不相同，在下面我們討論顯示的信息時(shí)會(huì)提到這一點(diǎn)。）
那么，這些信息的具體含意是什么呢？下面我們就來詳細(xì)地進(jìn)行分析。
set statistics time的結(jié)果
set statistics time命令用于測(cè)試各種操作的運(yùn)行時(shí)間，其中一些可能對(duì)于查詢性能的調(diào)節(jié)沒有什么用處。運(yùn)行這一命令可以在屏幕上得到如下的顯示信息：
輸出的最開始處：
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.
輸出的結(jié)束處：
sql server execution times:
cpu time = 30 ms, elapsed time = 387 ms.
在輸出的最開始處我們可以看到二次測(cè)試時(shí)間，但第一行執(zhí)行某一操作所需的cpu的時(shí)間和總共時(shí)間，但第二行似乎就不是了。
“sql server parse and compile time”表示sql server解析“elect * from [order details]”命令并將解析的結(jié)果放到sql server的過程緩沖區(qū)中供sql server使用所需要的cpu運(yùn)行時(shí)間和總的時(shí)間。
在本例中，cpu的運(yùn)行時(shí)間為10毫秒，總時(shí)間為61毫秒。由于服務(wù)器的配置和負(fù)載不同，你得到的cpu運(yùn)行時(shí)間、總時(shí)間這二個(gè)值可能會(huì)與本例中的測(cè)試結(jié)果有所不同。
第二行的“sql server parse and compile time”表示sql server從過程緩沖區(qū)中取出解析結(jié)果供執(zhí)行的時(shí)間，大多數(shù)情況下這二個(gè)值都會(huì)是0，因?yàn)檫@個(gè)過程執(zhí)行得相當(dāng)?shù)乜臁?br>
如果不清除緩沖區(qū)而再次運(yùn)行select * from [order details]命令，cpu運(yùn)行時(shí)間和編譯時(shí)間會(huì)都是0，因?yàn)閟ql server會(huì)重復(fù)使用緩沖區(qū)中的解析結(jié)果，因此就不需要再次編譯的時(shí)間了。
這些信息在查詢性能的調(diào)節(jié)中對(duì)你的幫助真的很大嗎？也許并非如此，但我將解釋一下這些信息的真正含意，你將會(huì)很驚奇，大多數(shù)的dba居然都不真正明白這些信息的含意：
我們最感興趣的是顯示在輸出最后的時(shí)間信息：
sql server execution times:
cpu time = 30 ms, elapsed time = 387 ms.
上面顯示的信息表明，執(zhí)行這次查詢使用了多少cpu運(yùn)行時(shí)間和運(yùn)行查詢使用了多少時(shí)間。cpu運(yùn)行時(shí)間是對(duì)運(yùn)行查詢所需要的cpu資源的一種相對(duì)穩(wěn)定的測(cè)量方法，與cpu的忙閑程度沒有關(guān)系。但是，每次運(yùn)行查詢時(shí)這一數(shù)字也會(huì)有所不同，只是變化的范圍沒有總時(shí)間變化大。總時(shí)間是對(duì)查詢執(zhí)行所需要的時(shí)間（不計(jì)算阻塞或讀數(shù)據(jù)的時(shí)間），由于服務(wù)器上的負(fù)載是在不斷變化的，因此這一數(shù)據(jù)的變化范圍有時(shí)會(huì)相當(dāng)?shù)卮蟆?br>
由于cpu占用時(shí)間是相對(duì)穩(wěn)定的，因此可以使用這一數(shù)據(jù)作為衡量你的調(diào)節(jié)措施是提高了查詢性能還是降低了查詢的性能的一種方法。
set statistics io的效果
set statistics io的輸出信息顯示在輸出的結(jié)束處，下面是它顯示的一個(gè)例子：
table 'order details'. scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.
這些信息中的一部分是十分有用的，另一部分則不然，我們來看看每個(gè)部分并了解其含意：
scan count：在查詢中涉及到的表被訪問的次數(shù)。在我們的例子中，其中的表只被訪問了1次，由于查詢中不包括連接命令，這一信息并不是十分有用，但如果查詢中包含有一個(gè)或多個(gè)連接，則這一信息是十分有用的。
一個(gè)循環(huán)外部的表的scan count值為1，但對(duì)于一個(gè)循環(huán)內(nèi)的表而言，其值為循環(huán)的次數(shù)。可以想象得到，對(duì)于一個(gè)循環(huán)內(nèi)的表而言，其scan count值越小，它所使用的資源越少，查詢的性能也就越高。因此在調(diào)節(jié)一個(gè)帶連接的查詢的性能時(shí)，需要關(guān)注scan count的值，在進(jìn)行調(diào)節(jié)時(shí)，注意觀察它是增加還是減少了。
logical reads: 這是set statistics io或set statistics time命令提供的最有用的數(shù)據(jù)。我們知道，sql server在可以對(duì)任何數(shù)據(jù)進(jìn)行操作前，必須首先把數(shù)據(jù)讀取到其數(shù)據(jù)緩沖區(qū)中。此外，我們也知道sql server何時(shí)會(huì)從數(shù)據(jù)緩沖區(qū)中讀取數(shù)據(jù)，并把數(shù)據(jù)讀取到大小為8k字節(jié)的頁中。
那么logical reads的意義是什么呢？logical reads是指sql server為得到查詢中的結(jié)果而必須從數(shù)據(jù)緩沖區(qū)讀取的頁數(shù)。在執(zhí)行查詢時(shí)，sql server不會(huì)讀取比實(shí)際需求多或少的數(shù)據(jù)，因此，當(dāng)在相同的數(shù)據(jù)集上執(zhí)行同一個(gè)查詢，得到的logical reads的數(shù)字總是相同的。
為什么說在調(diào)節(jié)查詢性能中知道sql server執(zhí)行查詢時(shí)的logical reads值是很重要的呢？因?yàn)樵诿看螆?zhí)行同一查詢時(shí)，這個(gè)數(shù)值是不會(huì)變化的。因此，在進(jìn)行查詢性能的調(diào)節(jié)時(shí)，這是一個(gè)可以用來衡量你的調(diào)節(jié)措施是否成功的一個(gè)很好的標(biāo)準(zhǔn)。
在對(duì)查詢的性能進(jìn)行調(diào)節(jié)時(shí)，如果logical reads值下降，就表明查詢使用的服務(wù)器資源減少，查詢的性能有所提高。如果logical reads值增加，則表示調(diào)節(jié)措施降低了查詢的性能。在其他條件不變的情況下，一個(gè)查詢使用的邏輯讀越少，其效率就越高，查詢的速度就越快。
physical reads：在這里我要說的的東西可能初聽起來有點(diǎn)自相矛盾，但只要反復(fù)思考，就會(huì)明白其中的真正含意。
物理讀指的是，在執(zhí)行真正的查詢操作前，sql server必須從磁盤上向數(shù)據(jù)緩沖區(qū)中讀取它所需要的數(shù)據(jù)。在sql server開始執(zhí)行查詢前，它要作的第一件事就是檢查它所需要的數(shù)據(jù)是否在數(shù)據(jù)緩沖區(qū)中，如果在，就從中讀取，如果不在，sql server必須首先將它需要的數(shù)據(jù)從磁盤上讀到數(shù)據(jù)緩沖區(qū)中。
我們可以想象得到，sql server在執(zhí)行物理讀時(shí)比執(zhí)行邏輯讀需要更多的服務(wù)器資源。因此，在理想情況下，我們應(yīng)當(dāng)盡量避免物理讀操作。
下面的這一部分聽起來讓人容易感到糊涂了。在對(duì)查詢的性能進(jìn)行調(diào)節(jié)時(shí)，可以忽略物理讀而只專注于邏輯讀。你一定會(huì)納悶兒，剛才不是還說物理讀比邏輯讀需要更多的服務(wù)器資源嗎？
情況確實(shí)是這樣，sql server在執(zhí)行查詢時(shí)所需要的物理讀次數(shù)不可能通過性能調(diào)節(jié)而減少的。減少物理讀的次數(shù)是dba的一項(xiàng)重要工作，但它涉及到整個(gè)服務(wù)器性能的調(diào)節(jié)，而不僅僅是查詢性能的調(diào)節(jié)。在進(jìn)行查詢性能調(diào)節(jié)時(shí)，我們不能控制數(shù)據(jù)緩沖區(qū)的大小或服務(wù)器的忙碌程度以及完成查詢所需要的數(shù)據(jù)是在數(shù)據(jù)緩沖區(qū)中還是在磁盤上，唯一我們能夠控制的數(shù)據(jù)是得到查詢結(jié)果所需要執(zhí)行的邏輯讀的次數(shù)。
因此，在查詢性能的調(diào)節(jié)中，我們可以心安理得地不理會(huì)set statistics io命令提供的physical read的值。（減少物理讀次數(shù)、加快sql server運(yùn)行速度的一種方式是確保服務(wù)器的物理內(nèi)存足夠多。）
read-ahead reads：與physical reads一樣，這個(gè)值在查詢性能調(diào)節(jié)中也沒有什么用戶。read-ahead reads表示sql server在執(zhí)行預(yù)讀機(jī)制時(shí)讀取的物理頁。為了優(yōu)化其性能，sql server在認(rèn)為它需要數(shù)據(jù)之前預(yù)先讀取一部分?jǐn)?shù)據(jù)，根據(jù)sql server對(duì)數(shù)據(jù)需求預(yù)測(cè)的準(zhǔn)確程度，預(yù)讀的數(shù)據(jù)頁可能有用，也可能沒用。
在本例中，read-ahead reads的值為9，physical read的值為1，而logical reads的值為10，它們之間存在著簡(jiǎn)單的相加關(guān)系。那么我在服務(wù)器上執(zhí)行查詢時(shí)的過程是怎么樣的呢？首先，sql server會(huì)開始檢查完成查詢所需要的數(shù)據(jù)是否在數(shù)據(jù)緩沖區(qū)中，它會(huì)很快地發(fā)現(xiàn)這些數(shù)據(jù)不在數(shù)據(jù)緩沖區(qū)中，并啟動(dòng)預(yù)讀機(jī)制將它所需要的10個(gè)數(shù)據(jù)頁中的前9個(gè)讀取到數(shù)據(jù)緩沖區(qū)。當(dāng)sql server檢查是否所需要的全部數(shù)據(jù)都已經(jīng)在數(shù)據(jù)緩沖區(qū)時(shí)，會(huì)發(fā)現(xiàn)已經(jīng)有9個(gè)數(shù)據(jù)頁在數(shù)據(jù)緩沖區(qū)中，還有一個(gè)不在，它就會(huì)立即再次讀取磁盤，將所需要的頁讀到數(shù)據(jù)緩沖區(qū)。一旦所有的數(shù)據(jù)都在數(shù)據(jù)緩沖區(qū)后，sql server就可以處理查詢了。
我們應(yīng)該怎么辦？
我在本篇文章的開始曾提到，在對(duì)查詢的性能進(jìn)行調(diào)節(jié)時(shí)用一些科學(xué)的標(biāo)準(zhǔn)來測(cè)量你的調(diào)節(jié)措施是否有效是十分重要的。問題是，sql servers的負(fù)載是動(dòng)態(tài)變化的，使用查詢總的運(yùn)行時(shí)間來衡量你正在調(diào)節(jié)性能的查詢的性能是提高了還是沒有，并不是一個(gè)合理的方法。
更好的方法是比較多個(gè)數(shù)據(jù)，例如邏輯讀的次數(shù)或者查詢所使用的cpu時(shí)間。因此在對(duì)查詢的性能進(jìn)行調(diào)節(jié)時(shí)，需要首先使用set statistics io和set statistics time命令向你提供一些必要的數(shù)據(jù)，以便確定你對(duì)查詢性能進(jìn)行調(diào)節(jié)的措施是否真正地得到了目的。
