看懂SqlServer查詢計劃

1、.看懂SqlServer查詢計劃對于SqlServer的優(yōu)化來說，可能優(yōu)化查詢是很常見的事情。關(guān)于數(shù)據(jù)庫的優(yōu)化，本身也是一個涉及面比較的廣的話題，本文只談優(yōu)化查詢時如何看懂SqlServer查詢計劃。由于本人對SqlServer的認識有限，如有錯誤，也懇請您在發(fā)現(xiàn)后及時批評指正。首先，打開【SQL Server Management Studio】，輸入一個查詢語句看看SqlServer是如何顯示查詢計劃的吧。說明：本文所演示的數(shù)據(jù)庫，是本人寫的一個演示程序?qū)Ｓ玫臄?shù)據(jù)庫，可以在此網(wǎng)頁中下載。selectv.OrderID,v.CustomerID,v.CustomerName,v.OrderD

2、ate,v.SumMoney,v.Finished fromOrdersView asv wherev.OrderDate='2010-12-1'andv.OrderDate'2011-12-1'；其中，OrdersView是一個視圖，其定義如下：SELECTdbo.Orders.OrderID,dbo.Orders.CustomerID,dbo.Orders.OrderDate,dbo.Orders.SumMoney,dbo.Orders.Finished,ISNULL(dbo.Customers.CustomerName,N'')ASCust

3、omerName FROMdbo.Orders LEFT OUTER JOINdbo.Customers ONdbo.Orders.CustomerID=dbo.Customers.CustomerID對于前一句查詢，SqlServer給出的查詢計劃如下(點擊工具欄上的【顯示估計的執(zhí)行計劃】按鈕)：從這個圖，我們至少可以得到3個有用的信息：1.哪些執(zhí)行步驟花費的成本比較高。顯然，最右邊的二個步驟的成本是比較高的。2.哪些執(zhí)行步驟產(chǎn)生的數(shù)據(jù)量比較多。對于每個步驟所產(chǎn)生的數(shù)據(jù)量，SqlServer的執(zhí)行計劃是用【線條粗細】來表示的，因此也很容易地從分辨出來。3.每一步執(zhí)行了什么樣的動作。對于一個比

4、較慢的查詢來說，我們通常首先要知道哪些步驟的成本比較高，進而，可以嘗試一些改進的方法。一般來說，如果您不能通過：提高硬件性能或者調(diào)整OS,SqlServer的設(shè)置之類的方式來解決問題，那么剩下的可選方法通常也只有以下這些了：1.為【scan】這類操作增加相應(yīng)字段的索引。2.有時重建索引或許也是有效的，具體情形請參考后文。3.調(diào)整語句結(jié)構(gòu)，引導(dǎo)SqlServer采用其它的查詢方案去執(zhí)行。4.調(diào)整表結(jié)構(gòu)(分表或者分區(qū))。下面再來說說一些很重要的理論知識，這些內(nèi)容對于執(zhí)行計劃的理解是很有幫助的。Sql Server查找記錄的方法說到這里，不得不說SqlServer的索引了。SqlServer有二種索

5、引：聚集索引和非聚集索引。二者的差別在于：【聚集索引】直接決定了記錄的存放位置，或者說：根據(jù)聚集索引可以直接獲取到記錄?！痉蔷奂饕勘４媪硕€信息：1.相應(yīng)索引字段的值，2.記錄對應(yīng)聚集索引的位置(如果表沒有聚集索引則保存記錄指針)。因此，如果能通過【聚集索引】來查找記錄，顯然也是最快的。Sql Server會有以下方法來查找您需要的數(shù)據(jù)記錄：1.【Table Scan】：遍歷整個表，查找所匹配的記錄行。這個操作將會一行一行的檢查，當然，效率也是最差的。2.【Index Scan】：根據(jù)索引，從表中過濾出來一部分記錄，再查找所匹配的記錄行，顯示比第一種方式的查找范圍要小，因此比【Table

6、Scan】要快。3.【Index Seek】：根據(jù)索引，定位(獲取)記錄的存放位置，然后取得記錄，因此，比起前二種方式會更快。4.【Clustered Index Scan】：和【Table Scan】一樣。注意：不要以為這里有個Index，就認為不一樣了。其實它的意思是說：按聚集索引來逐行掃描每一行記錄，因為記錄就是按聚集索引來順序存放的。而【Table Scan】只是說：要掃描的表沒有聚集索引而已，因此這二個操作本質(zhì)上也是一樣的。5.【Clustered Index Seek】：直接根據(jù)聚集索引獲取記錄，最快！所以，當發(fā)現(xiàn)某個查詢比較慢時，可以首先檢查哪些操作的成本比較高，再看看那些操作是

7、查找記錄時，是不是【Table Scan】或者【Clustered Index Scan】，如果確實和這二種操作類型有關(guān)，則要考慮增加索引來解決了。不過，增加索引后，也會影響數(shù)據(jù)表的修改動作，因為修改數(shù)據(jù)表時，要更新相應(yīng)字段的索引。所以索引過多，也會影響性能。還有一種情況是不適合增加索引的：某個字段用0或1表示的狀態(tài)。例如可能有絕大多數(shù)是1，那么此時加索引根本就沒有意義。這時只能考慮為0或者1這二種情況分開來保存了，分表或者分區(qū)都是不錯的選擇。如果不能通過增加索引和調(diào)整表來解決，那么可以試試調(diào)整語句結(jié)構(gòu)，引導(dǎo)SqlServer采用其它的查詢方案去執(zhí)行。這種方法要求：1.對語句所要完成的功能很清

8、楚，2.對要查詢的數(shù)據(jù)表結(jié)構(gòu)很清楚，3.對相關(guān)的業(yè)務(wù)背景知識很清楚。如果能通過這種方法去解決，當然也是很好的解決方法了。不過，有時SqlServer比較智能，即使你調(diào)整語句結(jié)構(gòu)，也不會影響它的執(zhí)行計劃。如何比較二個同樣功能的語句的性能好壞呢，我建議采用二種方法：1.直接把二個查詢語句放在【SQL Server Management Studio】，然后去看它們的【執(zhí)行計劃】，SqlServer會以百分比的方式告訴你二個查詢的【查詢開銷】。這種方法簡單，通常也是可以參考的，不過，有時也會不準，具體原因請接著往下看(可能索引統(tǒng)計信息過舊)。2.根據(jù)真實的程序調(diào)用，寫相應(yīng)的測試代碼去調(diào)用：這種方法就

9、麻煩一些，但是它更能代表現(xiàn)實調(diào)用情況，得到的結(jié)果也是更具有參考價值的，因此也是值得的。Sql Server Join方式在Sql Server中，我們每個join命令，都會在內(nèi)部執(zhí)行時，采用三種更具體的方式來運行：1.【Nested Loops join】，如果一個聯(lián)接輸入很小，而另一個聯(lián)接輸入很大而且已在其聯(lián)接列上創(chuàng)建了索引，則索引Nested Loops連接是最快的聯(lián)接操作，因為它們需要的I/O和比較都最少。嵌套循環(huán)聯(lián)接也稱為"嵌套迭代"，它將一個聯(lián)接輸入用作外部輸入表(顯示為圖形執(zhí)行計劃中的頂端輸入)，將另一個聯(lián)接輸入用作內(nèi)部(底端)輸入表。外部循環(huán)逐行處理外部輸入表

10、。內(nèi)部循環(huán)會針對每個外部行執(zhí)行，在內(nèi)部輸入表中搜索匹配行。可以用下面的偽碼來理解：foreach(row r1 in outer table)foreach(row r2 in inner table)if(r1,r2符合匹配條件)output(r1,r2)；最簡單的情況是，搜索時掃描整個表或索引；這稱為"單純嵌套循環(huán)聯(lián)接"。如果搜索時使用索引，則稱為"索引嵌套循環(huán)聯(lián)接"。如果將索引生成為查詢計劃的一部分(并在查詢完成后立即將索引破壞)，則稱為"臨時索引嵌套循環(huán)聯(lián)接"。查詢優(yōu)化器考慮了所有這些不同情況。如果外部輸入較小而內(nèi)部輸入較大且

11、預(yù)先創(chuàng)建了索引，則嵌套循環(huán)聯(lián)接尤其有效。在許多小事務(wù)中(如那些只影響較小的一組行的事務(wù))，索引嵌套循環(huán)聯(lián)接優(yōu)于合并聯(lián)接和哈希聯(lián)接。但在大型查詢中，嵌套循環(huán)聯(lián)接通常不是最佳選擇。2.【Merge Join】，如果兩個聯(lián)接輸入并不小但已在二者聯(lián)接列上排序(例如，如果它們是通過掃描已排序的索引獲得的)，則合并聯(lián)接是最快的聯(lián)接操作。如果兩個聯(lián)接輸入都很大，而且這兩個輸入的大小差不多，則預(yù)先排序的合并聯(lián)接提供的性能與哈希聯(lián)接相近。但是，如果這兩個輸入的大小相差很大，則哈希聯(lián)接操作通?？斓枚?。合并聯(lián)接要求兩個輸入都在合并列上排序，而合并列由聯(lián)接謂詞的等效(ON)子句定義。通常，查詢優(yōu)化器掃描索引(如果在適

12、當?shù)囊唤M列上存在索引)，或在合并聯(lián)接的下面放一個排序運算符。在極少數(shù)情況下，雖然可能有多個等效子句，但只用其中一些可用的等效子句獲得合并列。由于每個輸入都已排序，因此Merge Join運算符將從每個輸入獲取一行并將其進行比較。例如，對于內(nèi)聯(lián)接操作，如果行相等則返回。如果行不相等，則廢棄值較小的行并從該輸入獲得另一行。這一過程將重復(fù)進行，直到處理完所有的行為止。合并聯(lián)接操作可以是常規(guī)操作，也可以是多對多操作。多對多合并聯(lián)接使用臨時表存儲行(會影響效率)。如果每個輸入中有重復(fù)值，則在處理其中一個輸入中的每個重復(fù)項時，另一個輸入必須重繞到重復(fù)項的開始位置。可以創(chuàng)建唯一索引告訴SqlServer不會

13、有重復(fù)值。如果存在駐留謂詞，則所有滿足合并謂詞的行都將對該駐留謂詞取值，而只返回那些滿足該駐留謂詞的行。合并聯(lián)接本身的速度很快，但如果需要排序操作，選擇合并聯(lián)接就會非常費時。然而，如果數(shù)據(jù)量很大且能夠從現(xiàn)有B樹索引中獲得預(yù)排序的所需數(shù)據(jù)，則合并聯(lián)接通常是最快的可用聯(lián)接算法。3.【Hash Join】，哈希聯(lián)接可以有效處理未排序的大型非索引輸入。它們對復(fù)雜查詢的中間結(jié)果很有用，因為：1.中間結(jié)果未經(jīng)索引(除非已經(jīng)顯式保存到磁盤上然后創(chuàng)建索引)，而且通常不為查詢計劃中的下一個操作進行適當?shù)呐判颉?.查詢優(yōu)化器只估計中間結(jié)果的大小。由于對于復(fù)雜查詢，估計可能有很大的誤差，因此如果中間結(jié)果比預(yù)期的大得

14、多，則處理中間結(jié)果的算法不僅必須有效而且必須適度弱化。哈希聯(lián)接可以減少使用非規(guī)范化。非規(guī)范化一般通過減少聯(lián)接操作獲得更好的性能，盡管這樣做有冗余之險(如不一致的更新)。哈希聯(lián)接則減少使用非規(guī)范化的需要。哈希聯(lián)接使垂直分區(qū)(用單獨的文件或索引代表單個表中的幾組列)得以成為物理數(shù)據(jù)庫設(shè)計的可行選項。哈希聯(lián)接有兩種輸入：生成輸入和探測輸入。查詢優(yōu)化器指派這些角色，使兩個輸入中較小的那個作為生成輸入。哈希聯(lián)接用于多種設(shè)置匹配操作：內(nèi)部聯(lián)接；左外部聯(lián)接、右外部聯(lián)接和完全外部聯(lián)接；左半聯(lián)接和右半聯(lián)接；交集；聯(lián)合和差異。此外，哈希聯(lián)接的某種變形可以進行重復(fù)刪除和分組，例如SUM(salary)GROUP B

15、Y department。這些修改對生成和探測角色只使用一個輸入。哈希聯(lián)接又分為3個類型：內(nèi)存中的哈希聯(lián)接、Grace哈希聯(lián)接和遞歸哈希聯(lián)接。內(nèi)存中的哈希聯(lián)接：哈希聯(lián)接先掃描或計算整個生成輸入，然后在內(nèi)存中生成哈希表。根據(jù)計算得出的哈希鍵的哈希值，將每行插入哈希存儲桶。如果整個生成輸入小于可用內(nèi)存，則可以將所有行都插入哈希表中。生成階段之后是探測階段。一次一行地對整個探測輸入進行掃描或計算，并為每個探測行計算哈希鍵的值，掃描相應(yīng)的哈希存儲桶并生成匹配項。Grace哈希聯(lián)接：如果生成輸入大于內(nèi)存，哈希聯(lián)接將分為幾步進行。這稱為"Grace哈希聯(lián)接"。每一步都分為生成階段和探測

16、階段。首先，消耗整個生成和探測輸入并將其分區(qū)(使用哈希鍵上的哈希函數(shù))為多個文件。對哈希鍵使用哈希函數(shù)可以保證任意兩個聯(lián)接記錄一定位于相同的文件對中。因此，聯(lián)接兩個大輸入的任務(wù)簡化為相同任務(wù)的多個較小的實例。然后將哈希聯(lián)接應(yīng)用于每對分區(qū)文件。遞歸哈希聯(lián)接：如果生成輸入非常大，以至于標準外部合并的輸入需要多個合并級別，則需要多個分區(qū)步驟和多個分區(qū)級別。如果只有某些分區(qū)較大，則只需對那些分區(qū)使用附加的分區(qū)步驟。為了使所有分區(qū)步驟盡可能快，將使用大的異步I/O操作以便單個線程就能使多個磁盤驅(qū)動器繁忙工作。在優(yōu)化過程中不能始終確定使用哪種哈希聯(lián)接。因此，SQL Server開始時使用內(nèi)存中的哈希聯(lián)接，

17、然后根據(jù)生成輸入的大小逐漸轉(zhuǎn)換到Grace哈希聯(lián)接和遞歸哈希聯(lián)接。如果優(yōu)化器錯誤地預(yù)計兩個輸入中哪個較小并由此確定哪個作為生成輸入，生成角色和探測角色將動態(tài)反轉(zhuǎn)。哈希聯(lián)接確保使用較小的溢出文件作為生成輸入。這一技術(shù)稱為"角色反轉(zhuǎn)"。至少一個文件溢出到磁盤后，哈希聯(lián)接中才會發(fā)生角色反轉(zhuǎn)。說明：您也可以顯式的指定聯(lián)接方式，SqlServer會盡量尊重您的選擇。比如你可以這樣寫：inner loop join,left outer merge join,inner hash join但是，我還是建議您不要這樣做，因為SqlServer的選擇基本上都是正確的，不信您可以試一下。好了

18、，說了一大堆理論東西，再來個實際的例子來解釋一下吧。更具體執(zhí)行過程前面，我給出一張圖片，它反映了SqlServer在執(zhí)行某個查詢的執(zhí)行計劃，但它反映的信息可能不太細致，當然，您可以把鼠標指標移動某個節(jié)點上，會有以下信息出現(xiàn)：剛好，我裝的是中文版的，上面都是漢字，我也不多說了。我要說的是另一種方式的執(zhí)行過程，比這個包含更多的執(zhí)行信息，而且是實際的執(zhí)行情況。(當然，您也可以繼續(xù)使用圖形方式，在運行查詢前點擊工具欄上的【包括實際的執(zhí)行計劃】按鈕)讓我們再次回到【SQL Server Management Studio】，輸入以下語句，然后執(zhí)行。set statistics profile on se

19、lectv.OrderID,v.CustomerID,v.CustomerName,v.OrderDate,v.SumMoney,v.Finished fromOrdersView asv wherev.OrderDate='2010-12-1'andv.OrderDate'2011-12-1'；注意：現(xiàn)在加了一句，【set statistics profile on】，得到的結(jié)果如下：可以從圖片上看到，執(zhí)行查詢后，得到二個表格，上面的表格顯示了查詢的結(jié)果，下面的表格顯示了查詢的執(zhí)行過程。相比本文的第一張圖片，這張圖片可能在直觀上不太友好，但是，它能反映更多的信

20、息，而且尤其在比較復(fù)雜的查詢時，可能看起來更容易，因為對于復(fù)雜的查詢，【執(zhí)行計劃】的步驟太多，圖形方式會造成圖形過大，不容易觀察。而且這張執(zhí)行過程表格能反映2個很有價值的數(shù)據(jù)(前二列)。還是來看看這個【執(zhí)行過程表格】吧。我來挑幾個重要的說一下?！綬ows】：表示在一個執(zhí)行步驟中，所產(chǎn)生的記錄條數(shù)。(真實數(shù)據(jù)，非預(yù)期)【Executes】：表示某個執(zhí)行步驟被執(zhí)行的次數(shù)。(真實數(shù)據(jù)，非預(yù)期)【Stmt Text】：表示要執(zhí)行的步驟的描述。【EstimateRows】：表示要預(yù)期返回多少行數(shù)據(jù)。在這個【執(zhí)行過程表格】中，對于優(yōu)化查詢來說，我認為前三列是比較重要的。對于前二列，我上面也解釋了，意思也很

21、清楚。前二列的數(shù)字也大致反映了那些步驟所花的成本，對于比較慢的查詢中，應(yīng)該留意它們?！維tmt Text】會告訴你每個步驟做了什么事情。對于這種表格，它所要表達的其實是一種樹型信息(一行就表示在圖形方式下的一個節(jié)點)，所以，我建議從最內(nèi)層開始去讀它們。做為示例，我來解釋一下這張表格它所表達的執(zhí)行過程。第5行：【Clustered Index Seek(OBJECT：(MyNorthwind.dbo.Customers.PK_Customers),SEEK：(MyNorthwind.dbo.Customers.CustomerID=MyNorthwind.dbo.Orders.CustomerI

22、D)ORDERED FORWARD)】，意思是說，SqlServer在對表Customers做Seek操作，而且是按照【Clustered Index Seek】的方式，對應(yīng)的索引是【PK_Customers】，seek的值來源于Orders.CustomerID第4行：【Clustered Index Scan(OBJECT：(MyNorthwind.dbo.Orders.PK_Orders),WHERE：(MyNorthwind.dbo.Orders.OrderDate='2010-12-01 00：00：00.000'ANDMyNorthwind.dbo.Orders.O

23、rderDate'2011-12-01 00：00：00.000')】，意思是說，SqlServer在對表Customers做Scan操作，即：最差的【表掃描】的方式，原因是，OrderDate列上沒有索引，所以只能這樣了。第3行：【Nested Loops(Left Outer Join,OUTER REFERENCES：(MyNorthwind.dbo.Orders.CustomerID)】，意思是說，SqlServer把第5行和第4行產(chǎn)生的數(shù)據(jù)用【Nested Loops】的方式聯(lián)接起來，其中Outer表是Orders，要聯(lián)接的匹配操作也在第5行中指出了。第2行：【Com

24、pute Scalar(DEFINE：(Expr1006=isnull(MyNorthwind.dbo.Customers.CustomerName,N'')】，意思是說，要執(zhí)行一個isnull()函數(shù)的調(diào)用。具體原因請參考本文前部分中給出視圖定義代碼。第1行：【SELECTv.OrderID,v.CustomerID,v.CustomerName,v.OrderDate,v.SumMoney,v.FinishedFROMOrdersViewvWHEREv.OrderDate=1 ANDv.OrderDate2】，通常第1行就是整個查詢，表示它的返回值。索引統(tǒng)計信息：查詢計劃的

25、選擇依據(jù)前面一直說到【執(zhí)行計劃】，既然是計劃，就表示要在具體執(zhí)行前就能確定下來的操作方案。那么SqlServer是如何選擇一種執(zhí)行計劃的呢?SqlServer怎么知道什么時候該用索引或者用哪個索引?對于SqlServer來說，每當要執(zhí)行一個查詢時，都要首先檢查有沒有這個查詢的執(zhí)行計劃是否存在緩存中，如果沒有，則要生成一個執(zhí)行計劃，具體在產(chǎn)生執(zhí)行計劃時，并不是看有哪些索引可用(隨機選擇)，而是會參考一種被稱為【索引統(tǒng)計信息】的數(shù)據(jù)。如果您仔細地看一下前面的執(zhí)行計劃或者執(zhí)行過程表格，會發(fā)現(xiàn)SqlServer能預(yù)估每個步驟所產(chǎn)生的數(shù)據(jù)量，正是因為SqlServer能預(yù)估這些數(shù)據(jù)量，SqlServer

26、才能選擇一個它認為最合適的方法去執(zhí)行查詢過程，此時【索引統(tǒng)計信息】就能告訴SqlServer這些數(shù)據(jù)。說到這里，您是不是有點好奇呢，為了讓您對【索引統(tǒng)計信息】有個感性的認識，我們來看看【索引統(tǒng)計信息】是個什么樣子的。請在【SQL Server Management Studio】，輸入以下語句，然后執(zhí)行。dbccshow_statistics(Products,IX_CategoryID)得到的結(jié)果如下圖：再來看看命令的結(jié)果，它有三個表格組成：1.第一個表格，它列出了這個索引統(tǒng)計信息的主要信息。列名說明Name統(tǒng)計信息的名稱。Updated上一次更新統(tǒng)計信息的日期和時間。Rows表中的行數(shù)。R

27、ows Sampled統(tǒng)計信息的抽樣行數(shù)。Steps數(shù)據(jù)可分成多少個組，與第三個表對應(yīng)。Density第一個索引列前綴的選擇性(不包括EQ_ROWS)。Average key length所有索引列的平均長度。String Index如果為"是"，則統(tǒng)計信息中包含字符串摘要索引，以支持為LIKE條件估算結(jié)果集大小。僅適用于char、varchar、nchar和nvarchar、varchar(max)、nvarchar(max)、text以及ntext數(shù)據(jù)類型的前導(dǎo)列。2.第二個表格，它列出各種字段組合的選擇性，數(shù)據(jù)越小表示重復(fù)越性越小，當然選擇性也就越高。列名說明All

28、density索引列前綴集的選擇性(包括EQ_ROWS)。注意：這個值越小就表示選擇性越高。如果這個值小于0.1，這個索引的選擇性就比較高，反之，則表示選擇性就不高了。Average length索引列前綴集的平均長度。Columns為其顯示All density和Average length的索引列前綴的名稱。3.第三個表格，數(shù)據(jù)分布的直方圖，SqlServer就是靠它預(yù)估一些執(zhí)行步驟的數(shù)據(jù)量。列名說明RANGE_HI_KEY每個組中的最大值。RANGE_ROWS每組數(shù)據(jù)組的估算行數(shù)，不包含最大值。EQ_ROWS每組數(shù)據(jù)組中與最大值相等的行的估算數(shù)目。DISTINCT_RANGE_ROWS每

29、組數(shù)據(jù)組中的非重復(fù)值的估算數(shù)目，不包含最大值。AVG_RANGE_ROWS每組數(shù)據(jù)組中的重復(fù)值的平均數(shù)目，不包含最大值，計算公式：RANGE_ROWS/DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS 0為了能讓您更好的理解這些數(shù)據(jù)，尤其是第三組，請看下圖：當時我在填充測試數(shù)據(jù)時，故意把CategoryId為1到8的組，每組取了78條數(shù)據(jù)。所以【索引統(tǒng)計信息】的第三個表格的數(shù)據(jù)也都是正確的，也正是根據(jù)這些統(tǒng)計信息，SqlServer才能對每個執(zhí)行步驟預(yù)估相應(yīng)的數(shù)據(jù)量，從而影響Join之類的選擇。當然了，在選擇Join方式時，也要參考第二個表格中的字段選擇性

30、。最終在為新的查詢生成執(zhí)行計劃時，查詢優(yōu)化器使用這些統(tǒng)計信息并通過估計使用索引評估查詢的開銷來確定最佳查詢計劃。再來個例子來說明一下統(tǒng)計信息對于查詢計劃選擇的重要性。首先多加點數(shù)據(jù)，請看以下代碼：declarenewCategoryId int；insert intodbo.Categories(CategoryName)values(N'Test statistics')；setnewCategoryId=scope_identity()；declarecount int；setcount=0；while(count 100000)begin insert intoProdu

31、cts(ProductName,CategoryID,Unit,UnitPrice,Quantity,Remark)values(cast(newid()as nvarchar(50),newCategoryId,N'個',100,count+1,N'')；setcount=count+1；endgo update statisticsProducts；go再來看看索引統(tǒng)計信息：再來看看同一個查詢，但因為查詢參數(shù)值不同時，SqlServer選擇的執(zhí)行計劃：selectp.ProductId,t.Quantity fromProducts asp left out

32、er joinOrder Detailsast onp.ProductId=t.ProductId wherep.CategoryId=26；-26就是最新產(chǎn)生的CategoryId，因此這個查詢會返回10W條記錄selectp.ProductId,t.Quantity fromProducts asp left outer joinOrder Detailsast onp.ProductId=t.ProductId wherep.CategoryId=6；-這個查詢會返回95條記錄從上圖可以看出，由于CategoryId的參數(shù)值不同，SqlServer會選擇完全不同的執(zhí)行計劃。統(tǒng)計信息重要性

33、在這里體現(xiàn)地很清楚吧。創(chuàng)建統(tǒng)計信息后，數(shù)據(jù)庫引擎對列值(根據(jù)這些值創(chuàng)建統(tǒng)計信息)進行排序，并根據(jù)這些值(最多200個，按間隔分隔開)創(chuàng)建一個"直方圖"。直方圖指定有多少行精確匹配每個間隔值，有多少行在間隔范圍內(nèi)，以及間隔中值的密度大小或重復(fù)值的發(fā)生率。SQL Server 2005引入了對char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max)、text和ntext列創(chuàng)建的統(tǒng)計信息收集的其他信息。這些信息稱為"字符串摘要"，可以幫助查詢優(yōu)化器估計字符串模式中查詢謂詞的選擇性。查詢中有LIKE條件時，使用

34、字符串摘要可以更準確地估計結(jié)果集大小，并不斷優(yōu)化查詢計劃。這些條件包括諸如WHERE ProductName LIKE'%Bike'和WHERE Name LIKE'CSheryl'之類的條件。既然【索引統(tǒng)計信息】這么重要，那么它會在什么時候生成或者更新呢?事實上，【索引統(tǒng)計信息】是不用我們手工去維護的，SqlServer會自動去維護它們。而且在SqlServer中也有個參數(shù)來控制這個更新方式：統(tǒng)計信息自動功能工作方式創(chuàng)建索引時，查詢優(yōu)化器自動存儲有關(guān)索引列的統(tǒng)計信息。另外，當AUTO_CREATE_STATISTICS數(shù)據(jù)庫選項設(shè)置為ON(默認值)時，數(shù)據(jù)庫引

35、擎自動為沒有用于謂詞的索引的列創(chuàng)建統(tǒng)計信息。隨著列中數(shù)據(jù)發(fā)生變化，索引和列的統(tǒng)計信息可能會過時，從而導(dǎo)致查詢優(yōu)化器選擇的查詢處理方法不是最佳的。例如，如果創(chuàng)建一個包含一個索引列和1,000行數(shù)據(jù)的表，每一行在索引列中的值都是唯一的，則查詢優(yōu)化器將把該索引列視為收集查詢數(shù)據(jù)的好方法。如果更新列中的數(shù)據(jù)后存在許多重復(fù)值，則該列不再是用于查詢的理想候選列。但是，查詢優(yōu)化器仍然根據(jù)索引的過時分布統(tǒng)計信息(基于更新前的數(shù)據(jù))，將其視為好的候選列。當AUTO_UPDATE_STATISTICS數(shù)據(jù)庫選項設(shè)置為ON(默認值)時，查詢優(yōu)化器會在表中的數(shù)據(jù)發(fā)生變化時自動定期更新這些統(tǒng)計信息。每當查詢執(zhí)行計劃中使

36、用的統(tǒng)計信息沒有通過針對當前統(tǒng)計信息的測試時就會啟動統(tǒng)計信息更新。采樣是在各個數(shù)據(jù)頁上隨機進行的，取自表或統(tǒng)計信息所需列的最小非聚集索引。從磁盤讀取一個數(shù)據(jù)頁后，該數(shù)據(jù)頁上的所有行都被用來更新統(tǒng)計信息。常規(guī)情況是：在大約有20%的數(shù)據(jù)行發(fā)生變化時更新統(tǒng)計信息。但是，查詢優(yōu)化器始終確保采樣的行數(shù)盡量少。對于小于8 MB的表，則始終進行完整掃描來收集統(tǒng)計信息。采樣數(shù)據(jù)(而不是分析所有數(shù)據(jù))可以將統(tǒng)計信息自動更新的開銷降至最低。在某些情況下，統(tǒng)計采樣無法獲得表中數(shù)據(jù)的精確特征?？梢允褂肬PDATE STATISTICS語句的SAMPLE子句和FULLSCAN子句，控制按逐個表的方式手動更新統(tǒng)計信息時

37、采樣的數(shù)據(jù)量。FULLSCAN子句指定掃描表中的所有數(shù)據(jù)來收集統(tǒng)計信息，而SAMPLE子句用來指定采樣的行數(shù)百分比或采樣的行數(shù)在SQL Server 2005中，數(shù)據(jù)庫選項AUTO_UPDATE_STATISTICS_ASYNC提供了統(tǒng)計信息異步更新功能。當此選項設(shè)置為ON時，查詢不等待統(tǒng)計信息更新，即可進行編譯。而過期的統(tǒng)計信息置于隊列中，由后臺進程中的工作線程來更新。查詢和任何其他并發(fā)查詢都通過使用現(xiàn)有的過期統(tǒng)計信息立即編譯。由于不存在等待更新后的統(tǒng)計信息的延遲，因此查詢響應(yīng)時間可預(yù)測；但是過期的統(tǒng)計信息可能導(dǎo)致查詢優(yōu)化器選擇低效的查詢計劃。在更新后的統(tǒng)計信息就緒后啟動的查詢將使用那些統(tǒng)計

38、信息。這可能會導(dǎo)致重新編譯緩存的計劃(取決于較舊的統(tǒng)計信息版本)。如果在同一個顯式用戶事務(wù)中出現(xiàn)某些數(shù)據(jù)定義語言(DDL)語句(例如，CREATE、ALTER和DROP語句)，則無法更新異步統(tǒng)計信息。AUTO_UPDATE_STATISTICS_ASYNC選項設(shè)置于數(shù)據(jù)庫級別，并確定用于數(shù)據(jù)庫中所有統(tǒng)計信息的更新方法。它只適用于統(tǒng)計信息更新，而無法用于以異步方式創(chuàng)建統(tǒng)計信息。只有將AUTO_UPDATE_STATISTICS設(shè)置為ON時，將此選項設(shè)置為ON才有效。默認情況下，AUTO_UPDATE_STATISTICS_ASYNC選項設(shè)置為OFF。從以上說明中，我們可以看出，對于大表，還是有可能存在統(tǒng)計信息更新不及時的時候，這時，就可能會影響查詢優(yōu)化器的判斷了。有些人可能有個經(jīng)驗：對于一些慢的查詢，他們會想到重建索引來嘗試解決。其實這樣做是有道理的。因為，在某些時候一個查詢突然變慢了，可能和統(tǒng)計信息更新不及時有關(guān)，進而會影響查詢優(yōu)化器的判斷。如果此時重建索引，就可以讓查詢優(yōu)化器知道最新的數(shù)據(jù)分布，自然就可以避開這個問題。還記得我前面用