SqlServer查詢計劃

標籤：blog   http   os   io   使用   ar   strong   for   檔案   

閱讀目錄

• 開始
• SQL Server 尋找記錄的方法
• SQL Server Join 方式
• 更具體執行過程
• 索引統計資訊：查詢計劃的選擇依據
• 最佳化視圖查詢
• 推薦閱讀-MSDN文章

對於SQL Server的最佳化來說，最佳化查詢可能是很常見的事情。由於資料庫的最佳化，本身也是一個涉及面比較的廣的話題， 因此本文只談最佳化查詢時如何看懂SQL Server查詢計劃。畢竟我對SQL Server的認識有限，如有錯誤，也懇請您在發現後及時批評指正。

首先，開啟【SQL Server Management Studio】，輸入一個查詢語句看看SQL Server是如何顯示查詢計劃的吧。
說明：本文所示範的資料庫，是我為一個示範程式專用準備的資料庫， 可以在此網頁中下載。

select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Finishedfrom   OrdersView as vwhere v.OrderDate >= ‘2010-12-1‘ and v.OrderDate < ‘2011-12-1‘;

其中，OrdersView是一個視圖，其定義如下：

SELECT     dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate,             dbo.Orders.SumMoney, dbo.Orders.Finished,             ISNULL(dbo.Customers.CustomerName, N‘‘) AS CustomerNameFROM         dbo.Orders LEFT OUTER JOIN                dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

對於前一句查詢，SQL Server給出的查詢計劃如下（點擊工具列上的【顯示估計的執行計畫】按鈕）：

從這個圖中，我們至少可以得到3個有用的資訊：
1. 哪些執行步驟花費的成本比較高。顯然，最右邊的二個步驟的成本是比較高的。
2. 哪些執行步驟產生的資料量比較多。對於每個步驟所產生的資料量， SQL Server的執行計畫是用【線條寬度】來表示的，因此也很容易地從分辨出來。
3. 每一步執行了什麼樣的動作。

對於一個比較慢的查詢來說，我們通常要知道哪些步驟的成本比較高，進而，可以嘗試一些改進的方法。 一般來說，如果您不能通過：提高硬體效能或者調整OS,SQL Server的設定之類的方式來解決問題，那麼剩下的可選方法通常也只有以下這些了：
1. 為【scan】這類操作增加相應欄位的索引。
2. 有時重建索引或許也是有效，具體情形請參考後文。
3. 調整語句結構，引導SQL Server採用其它的查詢方案去執行。
4. 調整表結構（分表或者分區）。

下面再來說說一些很重要的理論知識，這些內容對於執行計畫的理解是很有協助的。

回到頂部SQL Server 尋找記錄的方法

說到這裡，不得不說SQL Server的索引了。SQL Server有二種索引：叢集索引和非叢集索引。二者的差別在於：【叢集索引】直接決定了記錄的存放位置， 或者說：根據叢集索引可以直接擷取到記錄。【非叢集索引】儲存了二個資訊：1.相應索引欄位的值，2.記錄對應叢集索引的位置（如果表沒有叢集索引則儲存記錄指標）。 因此，如果能通過【叢集索引】來尋找記錄，顯然也是最快的。

SQL Server 會有以下方法來尋找您需要的資料記錄：
1. 【Table Scan】：遍曆整個表，尋找所有匹配的記錄行。這個操作將會一行一行的檢查，當然，效率也是最差的。
2. 【Index Scan】：根據索引，從表中過濾出來一部分記錄，再尋找所有匹配的記錄行，顯然比第一種方式的尋找範圍要小，因此比【Table Scan】要快。
3. 【Index Seek】：根據索引，定位（擷取）記錄的存放位置，然後取得記錄，因此，比起前二種方式會更快。
4. 【Clustered Index Scan】：和【Table Scan】一樣。注意：不要以為這裡有個Index，就認為不一樣了。 其實它的意思是說：按叢集索引來漸進式掃描每一行記錄，因為記錄就是按叢集索引來順序存放的。 而【Table Scan】只是說：要掃描的表沒有叢集索引而已，因此這二個操作本質上也是一樣的。
5. 【Clustered Index Seek】：直接根據叢集索引擷取記錄，最快！

所以，當發現某個查詢比較慢時，可以首先檢查哪些操作的成本比較高，再看看那些操作在尋找記錄時， 是不是【Table Scan】或者【Clustered Index Scan】，如果確實和這二種操作類型有關，則要考慮增加索引來解決了。 不過，增加索引後，也會影響資料表的修改動作，因為修改資料表時，要更新相應欄位的索引。所以索引過多，也會影響效能。 還有一種情況是不適合增加索引的：某個欄位用0或1表示的狀態。例如可能有絕大多數是1，那麼此時加索引根本就沒有意義。 這時只能考慮為0或者1這二種情況分開來儲存了，分表或者分區都是不錯的選擇。

如果不能通過增加索引和調整表來解決，那麼可以試試調整語句結構，引導SQL Server採用其它的查詢方案去執行。 這種方法要求： 1.對語句所要完成的功能很清楚， 2.對要查詢的資料表結構很清楚， 3.對相關的業務背景知識很清楚。 如果能通過這種方法去解決，當然也是很好的解決方案了。不過，有時SQL Server比較智能，即使你調整語句結構，也不會影響它的執行計畫。

如何比較二個相同功能的SQL語句的效能好壞呢，我建議採用二種方法： 1. 直接把二個查詢語句放在【SQL Server Management Studio】，然後去看它們的【執行計畫】，SQL Server會以百分比的方式告訴你二個查詢的【查詢開銷】。 這種方法簡單，通常也是可以參考的，不過，有時也會不準，具體原因請接著往下看(可能索引統計資訊過舊)。
2. 根據真實的程式調用，寫相應的測試代碼去調用：這種方法就麻煩一些，但是它更能代表現實調用情況， 得到的結果也是更具有參考價值的，因此也是值得的。

回到頂部SQL Server Join 方式

在SQL Server中，每個join命令，都會在內部執行時採用三種更具體的方式來運行：

1. 【Nested Loops join】，如果一個聯結輸入很小，而另一個聯結輸入很大而且已在其聯結列上建立了索引， 則索引 Nested Loops 串連是最快的聯結操作，因為它們需要的 I/O 和比較都最少。

巢狀迴圈聯結也稱為“嵌套迭代”，它將一個聯結輸入用作外部輸入表（顯示為圖形執行計畫中的頂端輸入），將另一個聯結輸入用作內部（底端）輸入表。外部迴圈逐行處理外部輸入表。內部迴圈會針對每個外部行執行，在內部輸入表中搜尋匹配行。可以用下面的偽碼來理解：

foreach(row r1 in outer table)    foreach(row r2 in inner table)        if( r1, r2 符合匹配條件 )            output(r1, r2);

最簡單的情況是，搜尋時掃描整個表或索引；這稱為“單純巢狀迴圈聯結”。如果搜尋時使用索引，則稱為“索引巢狀迴圈聯結”。如果將索引產生為查詢計劃的一部分（並在查詢完成後立即將索引破壞），則稱為“臨時索引巢狀迴圈聯結”。查詢最佳化工具考慮了所有這些不同情況。

如果外部輸入較小而內部輸入較大且預先建立了索引，則巢狀迴圈聯結尤其有效。在許多小事務中（如那些隻影響較小的一組行的事務），索引巢狀迴圈聯結優於合并聯結和雜湊聯結。但在大型查詢中，巢狀迴圈聯結通常不是最佳選擇。

2. 【Merge Join】，如果兩個聯結輸入並不小但已在二者聯結列上排序（例如，如果它們是通過掃描已排序的索引獲得的），則合并聯結是最快的聯結操作。如果兩個聯結輸入都很大，而且這兩個輸入的大小差不多，則預先排序的合并聯結提供的效能與雜湊聯結相近。但是，如果這兩個輸入的大小相差很大，則雜湊聯結操作通常快得多。

合并聯結要求兩個輸入都在合并列上排序，而合并列由聯結謂詞的等效 (ON) 子句定義。通常，查詢最佳化工具掃描索引（如果在適當的一組列上存在索引），或在合并聯結的下面放一個排序運算子。在極少數情況下，雖然可能有多個等效子句，但只用其中一些可用的等效子句獲得合并列。

由於每個輸入都已排序，因此 Merge Join 運算子將從每個輸入擷取一行並將其進行比較。例如，對於內聯結操作，如果行相等則返回。如果行不相等，則廢棄值較小的行並從該輸入獲得另一行。這一過程將重複進行，直到處理完所有的行為止。

合并聯結操作可以是常規操作，也可以是多對多操作。多對多合并聯結使用暫存資料表儲存行（會影響效率）。如果每個輸入中有重複值，則在處理其中一個輸入中的每個重複項時，另一個輸入必須重繞到重複項的開始位置。 可以建立唯一索引告訴SQL Server不會有重複值。

如果存在駐留謂詞，則所有滿足合并謂詞的行都將對該駐留謂詞取值，而只返回那些滿足該駐留謂詞的行。

合并聯結本身的速度很快，但如果需要排序操作，選擇合并聯結就會非常費時。然而，如果資料量很大且能夠從現有 B 樹索引中獲得預排序的所需資料，則合并聯結通常是最快的可用聯結演算法。

3. 【Hash Join】，雜湊聯結可以有效處理未排序的大型非索引輸入。它們對複雜查詢的中間結果很有用，因為： 1. 中間結果未經索引（除非已經顯式儲存到磁碟上然後建立索引），而且通常不為查詢計劃中的下一個操作進行適當的排序。 2. 查詢最佳化工具只估計中間結果的大小。由於對於複雜查詢，估計可能有很大的誤差，因此如果中間結果比預期的大得多，則處理中間結果的演算法不僅必須有效而且必須適度弱化。

雜湊聯結可以減少使用非正常化。非正常化一般通過減少聯結操作獲得更好的效能，儘管這樣做有冗餘之險（如不一致的更新）。雜湊聯結則減少使用非正常化的需要。雜湊聯結使垂直資料分割（用單獨的檔案或索引代表單個表中的幾組列）得以成為物理資料庫設計的可行選項。

雜湊聯結有兩種輸入：產生輸入和探測輸入。查詢最佳化工具指派這些角色，使兩個輸入中較小的那個作為產生輸入。

雜湊聯結用於多種設定匹配操作：內部聯結；左方外部聯結、右方外部聯結和完整外部聯結；左半聯結和右半聯結；交集；聯合和差異。此外，雜湊聯結的某種變形可以進行重複刪除和分組，例如 SUM(salary) GROUP BY department。這些修改對產生和探測角色只使用一個輸入。

雜湊聯結又分為3個類型：記憶體中的雜湊聯結、Grace 雜湊聯結和遞迴雜湊聯結。

記憶體中的雜湊聯結：雜湊聯結先掃描或計算整個產生輸入，然後在記憶體中產生雜湊表。根據計算得出的雜湊鍵的雜湊值，將每行插入雜湊儲存桶。如果整個產生輸入小於可用記憶體，則可以將所有行都插入雜湊表中。產生階段之後是探測階段。一次一行地對整個探測輸入進行掃描或計算，並為每個探測行計算雜湊鍵的值，掃描相應的雜湊儲存桶並產生匹配項。

Grace 雜湊聯結：如果產生輸入大於記憶體，雜湊聯結將分為幾步進行。這稱為“Grace 雜湊聯結”。每一步都分為產生階段和探測階段。首先，消耗整個產生和探測輸入並將其分區（使用雜湊鍵上的雜湊函數）為多個檔案。對雜湊鍵使用雜湊函數可以保證任意兩個聯結記錄一定位於相同的檔案對中。因此，聯結兩個大輸入的任務簡化為相同任務的多個較小的執行個體。然後將雜湊聯結應用於每對分區檔案。

遞迴雜湊聯結：如果產生輸入非常大，以至於標準外部合并的輸入需要多個合并層級，則需要多個分區步驟和多個分區層級。如果只有某些分區較大，則只需對那些分區使用附加的分區步驟。為了使所有分區步驟儘可能快，將使用大的非同步 I/O 操作以便單個線程就能使多個磁碟機繁忙工作。

在最佳化過程中不能始終確定使用哪種雜湊聯結。因此，SQL Server 開始時使用記憶體中的雜湊聯結，然後根據產生輸入的大小逐漸轉換到 Grace 雜湊聯結和遞迴雜湊聯結。 
如果最佳化器錯誤地預計兩個輸入中哪個較小並由此確定哪個作為產生輸入，產生角色和探測角色將動態反轉。雜湊聯結確保使用較小的溢出檔案作為產生輸入。這一技術稱為“角色反轉”。至少一個檔案溢出到磁碟後，雜湊聯結中才會發生角色反轉。

說明：您也可以顯式的指定聯結方式，SQL Server會盡量尊重您的選擇。比如你可以這樣寫：inner loop join, left outer merge join, inner hash join 
但是，我還是建議您不要這樣做，因為SQL Server的選擇基本上都是正確的，不信您可以試一下。

好了，說了一大堆理論東西，再來個實際的例子解釋一下吧。

回到頂部更具體執行過程

前面，我給出一張圖片，它反映了SQL Server在執行某個查詢的執行計畫，但它反映的資訊可能不太細緻，當然，您可以把滑鼠指標移動某個節點上，會有以下資訊出現：

剛好，我裝的是中文版的，上面都是漢字，我也不多說了。我要說的是另一種方式的執行過程，比這個包含更多的執行資訊， 而且是實際的執行情況。（當然，您也可以繼續使用圖形方式，在執行查詢前點擊工具列上的【包括實際的執行計畫】按鈕）

讓我們再次回到【SQL Server Management Studio】，輸入以下語句，然後執行。

set statistics profile on select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Finishedfrom   OrdersView as vwhere v.OrderDate >= ‘2010-12-1‘ and v.OrderDate < ‘2011-12-1‘;

注意：現在加了一句，【set statistics profile on 】，得到的結果如下：

可以從圖片上看到，執行查詢後，得到二個表格，上面的表格顯示了查詢的結果，下面的表格顯示了查詢的執行過程。相比本文的第一張圖片， 這張圖片可能在直觀上不太友好，但是，它能反映更多的資訊，而且尤其在比較複雜的查詢時，可能看起來更容易，因為對於複雜的查詢，【執行計畫】的步驟太多，圖形方式會造成圖形過大，不容易觀察。 而且這張執行過程表格能反映2個很有價值的資料（前二列）。

還是來看看這個【執行過程表格】吧。我來挑幾個重要的說一下。
【Rows】：表示在一個執行步驟中，所產生的記錄條數。（真實資料，非預期）
【Executes】：表示某個執行步驟被執行的次數。（真實資料，非預期）
【Stmt Text】：表示要執行的步驟的描述。
【EstimateRows】：表示要預期返回多少行資料。

在這個【執行過程表格】中，對於最佳化查詢來說，我認為前三列是比較重要的。對於前二列，我上面也解釋了，意思也很清楚。 前二列的數字也大致反映了那些步驟所花的成本，對於比較慢的查詢中，應該留意它們。 【Stmt Text】會告訴你每個步驟做了什麼事情。對於這種表格，它所要表達的其實是一種樹型資訊（一行就表示在圖形方式下的一個節點）， 所以，我建議從最內層開始去讀它們。做為樣本，我來解釋一下這張表格它所表達的執行過程。

第5行：【Clustered Index Seek(OBJECT:([MyNorthwind].[dbo].[Customers].[PK_Customers]), SEEK:([MyNorthwind].[dbo].[Customers].[CustomerID]=[MyNorthwind].[dbo].[Orders].[CustomerID]) ORDERED FORWARD)】， 意思是說，SQL Server在對錶Customers做Seek操作，而且是按照【Clustered Index Seek】的方式，對應的索引是【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‘ AND [MyNorthwind].[dbo].[Orders].[OrderDate]<‘2011-12-01 00:00:00.000‘))】， 意思是說，SQL Server在對錶Customers做Scan操作，即：最差的【表掃描】的方式，原因是，OrderDate列上沒有索引，所以只能這樣了。

第3行：【Nested Loops(Left Outer Join, OUTER REFERENCES:([MyNorthwind].[dbo].[Orders].[CustomerID]))】， 意思是說，SQL Server把第5行和第4行產生的資料用【Nested Loops】的方式聯結起來，其中Outer表是Orders，要聯結的匹配操作也在第5行中指出了。

第2行：【Compute Scalar(DEFINE:([Expr1006]=isnull([MyNorthwind].[dbo].[Customers].[CustomerName],N‘‘)))】， 意思是說，要執行一個isnull()函數的調用。具體原因請參考本文前部分中給出視圖定義代碼。

第1行：【SELECT [v].[OrderID],[v].[CustomerID],[v].[CustomerName],[v].[OrderDate],[v].[SumMoney],[v].[Finished] FROM [OrdersView] [v] WHERE [v].[OrderDate]>[email protected] AND [v].[OrderDate]<@2】， 通常第1行就是整個查詢，表示它的傳回值。

回到頂部索引統計資訊：查詢計劃的選擇依據

前面一直說到【執行計畫】，既然是計劃，就表示要在具體執行前就能確定下來的操作方案。那麼SQL Server是如何選擇一個執行計畫的呢？ SQL Server怎麼知道什麼時候該用索引或者用哪個索引呢？ 對於SQL Server來說，每當要執行一個查詢時，都要首先檢查這個查詢的執行計畫是否存在緩衝中，如果沒有，就要產生一個執行計畫， 具體在產生執行計畫時，並不是看有哪些索引可用（隨機播放），而是會參考一種被稱為【索引統計資訊】的資料。 如果您仔細地看一下前面的執行計畫或者執行過程表格，會發現SQL Server能預估每個步驟所產生的資料量， 正是因為SQL Server能預估這些資料量，SQL Server才能選擇一個它認為最合適的方法去執行查詢過程， 此時【索引統計資訊】就能告訴SQL Server這些資訊。 說到這裡，您是不是有點好奇呢，為了讓您對【索引統計資訊】有個感性的認識，我們來看看【索引統計資訊】是個什麼樣子的。 請在【SQL Server Management Studio】，輸入以下語句，然後執行。

dbcc show_statistics (Products, IX_CategoryID)

得到的結果如：

首先，還是解釋一下命令：【dbcc show_statistics】這個命令可以顯示我們想知道的【索引統計資訊】，它需要二個參數，1. 表名，2. 索引名

再來看看命令的結果，它有三個表格組成：
1. 第一個表格，它列出了這個索引統計資訊的主要資訊。

列名	說明
Name	統計資訊的名稱。
Updated	上一次更新統計資料的日期和時間。
Rows	表中的行數。
Rows Sampled	統計資訊的抽樣行數。
Steps	資料可分成多少個組，與第三個表對應。
Density	第一個索引列首碼的選擇性（不包括 EQ_ROWS）。
Average key length	所有索引列的平均長度。
String Index	如果為“是”，則統計資訊中包含字串摘要索引，以支援為 LIKE 條件估算結果集大小。僅適用於 char、varchar、nchar 和nvarchar、varchar(max)、nvarchar(max)、text 以及 ntext 資料類型的前置列。

2. 第二個表格，它列出各種欄位組合的選擇性，資料越小表示重複越性越小，當然選擇性也就越高。

列名	說明
All density	索引列首碼集的選擇性（包括 EQ_ROWS）。注意：這個值越小就表示選擇性越高。 如果這個值小於0.1，這個索引的選擇性就比較高，反之，則表示選擇性就不高了。
Average length	索引列首碼集的平均長度。
Columns	為其顯示 All density 和 Average length 的索引列首碼的名稱。

3. 第三個表格，資料分布的長條圖，SQL Server就是靠它預估一些執行步驟的資料量。

列名	說明
RANGE_HI_KEY	每個組中的最大值。
RANGE_ROWS	每組資料群組的估算行數，不包含最大值。
EQ_ROWS	每組資料群組中與最大值相等的行的估算數目。
DISTINCT_RANGE_ROWS	每組資料群組中的非重複值的估算數目，不包含最大值。
AVG_RANGE_ROWS	每組資料群組中的重複值的平均數目，不包含最大值，計算公式：RANGE_ROWS / DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS > 0

為了能讓您更好的理解這些資料，尤其是第三組，請看：

當時我在填充測試資料時，故意把CategoryId分為1到8（10是後來臨時加的），每組填充了78條資料。所以【索引統計資訊】的第三個表格的資料也都是正確的， 也正是根據這些統計資訊，SQL Server才能對每個執行步驟預估相應的資料量，從而影響Join之類的選擇。當然了，在選擇Join方式時， 也要參考第二個表格中欄位的選擇性。SQL Server在為查詢產生執行計畫時， 查詢最佳化工具將使用這些統計資訊並結合相關的索引來評估每種方案的開銷來選擇最佳的查詢計劃。

再來個例子說明一下統計資訊對於查詢計劃的重要性。首先多加點資料，請看以下代碼：

declare @newCategoryId int;insert into dbo.Categories (CategoryName) values(N‘Test statistics‘);set @newCategoryId = scope_identity();declare @count int;set @count = 0;while( @count < 100000 )begin    insert into Products (ProductName, CategoryID, Unit, UnitPrice, Quantity, Remark)     values( cast(newid() as nvarchar(50)), @newCategoryId, N‘個‘, 100, @count +1, N‘‘);    set @count = @count + 1;endgoupdate statistics Products;go

再來看看索引統計資訊：

再來看看同一個查詢，但因為查詢參數值不同時，SQL Server選擇的執行計畫：

select p.ProductId, t.Quantity from Products as p left outer join [Order Details] as t on p.ProductId = t.ProductId where p.CategoryId = 26;    -- 26 就是最新產生的CategoryId，因此這個查詢會返回10W條記錄select p.ProductId, t.Quantity from Products as p left outer join [Order Details] as t on p.ProductId = t.ProductId where p.CategoryId = 6;    -- 這個查詢會返回95條記錄

從可以看出，由於CategoryId的參數值不同，SQL Server會選擇完全不同的執行計畫。統計資訊重要性在這裡體現的很清楚吧。

建立統計資訊後，資料庫引擎對列值（根據這些值建立統計資訊）進行排序， 並根據這些值（最多 200 個，按間隔分隔開）建立一個“長條圖”。長條圖指定有多少行精確匹配每個間隔值， 有多少行在間隔範圍內，以及間隔中值的密度大小或重複值的發生率。

SQL Server 2005 引入了對 char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max)、text 和 ntext 列建立的統計資訊收集的其他資訊。這些資訊稱為“字串摘要”，可以協助查詢最佳化工具估計字串模式中查詢謂詞的選擇性。 查詢中有 LIKE 條件時，使用字串摘要可以更準確地估計結果集大小，並不斷最佳化查詢計劃。 這些條件包括諸如 WHERE ProductName LIKE ‘%Bike‘ 和 WHERE Name LIKE ‘[CS]heryl‘ 之類的條件。

既然【索引統計資訊】這麼重要，那麼它會在什麼時候產生或者更新呢？事實上，【索引統計資訊】是不用我們手工去維護的， SQL Server會自動去維護它們。而且在SQL Server中也有個參數來控制這個更新方式：

統計資訊自動功能工作方式

建立索引時，查詢最佳化工具自動儲存有關索引列的統計資訊。另外，當 AUTO_CREATE_STATISTICS 資料庫選項設定為 ON（預設值）時， 資料庫引擎自動為沒有用於謂詞的索引的列建立統計資訊。

隨著列中資料發生變化，索引和列的統計資訊可能會過時，從而導致查詢最佳化工具選擇的查詢處理方法不是最佳的。 例如，如果建立一個包含一個索引列和 1,000 行資料的表，每一行在索引列中的值都是唯一的， 則查詢最佳化工具將把該索引列視為收集查詢資料的好方法。如果更新列中的資料後存在許多重複值， 則該列不再是用於查詢的理想候選列。但是，查詢最佳化工具仍然根據索引的過時分布統計資訊（基於更新前的資料），將其視為好的候選列。

當 AUTO_UPDATE_STATISTICS 資料庫選項設定為 ON（預設值）時，查詢最佳化工具會在表中的資料發生變化時自動定期更新這些統計資訊。 每當查詢執行計畫中使用的統計資訊沒有通過針對當前統計資訊的測試時就會啟動統計資訊更新。 採樣是在各個資料頁上隨機進行的，取自表或統計資訊所需列的最小非叢集索引。 從磁碟讀取一個資料頁後，該資料頁上的所有行都被用來更新統計資料。 常規情況是：在大約有 20% 的資料行發生變化時更新統計資料。但是，查詢最佳化工具始終確保採樣的行數盡量少。 對於小於 8 MB 的表，則始終進行完整掃描來收集統計資訊。

採樣資料（而不是分析所有資料）可以將統計資訊自動更新的開銷降至最低。 在某些情況下，統計採樣無法獲得表中資料的精確特徵。可以使用 UPDATE STATISTICS 語句的 SAMPLE 子句和 FULLSCAN 子句， 控制按逐個表的方式手動更新統計資料時採樣的資料量。FULLSCAN 子句指定掃描表中的所有資料來收集統計資訊， 而 SAMPLE 子句用來指定採樣的行數百分比或採樣的行數

在 SQL Server 2005 中，資料庫選項 AUTO_UPDATE_STATISTICS_ASYNC 提供了統計資訊非同步更新功能。 當此選項設定為 ON 時，查詢不等待統計資訊更新，即可進行編譯。而到期的統計資訊置於隊列中， 由後台進程中的背景工作執行緒來更新。查詢和任何其他並發查詢都通過使用現有的到期統計資訊立即編譯。 由於不存在等待更新後的統計資訊的延遲，因此查詢回應時間可預測；但是到期的統計資訊可能導致查詢最佳化工具選擇低效的查詢計劃。 在更新後的統計資訊就緒後啟動的查詢將使用那些統計資訊。這可能會導致重新編譯緩衝的計劃（取決於較舊的統計資訊版本）。 如果在同一個顯式使用者事務中出現某些資料定義語言 (Data Definition Language) (DDL) 語句（例如，CREATE、ALTER 和 DROP 語句），則無法更新非同步統計資訊。

AUTO_UPDATE_STATISTICS_ASYNC 選項設定於資料庫層級，並確定用於資料庫中所有統計資訊的更新方法。 它只適用於統計資訊更新，而無法用於以非同步方式建立統計資訊。只有將 AUTO_UPDATE_STATISTICS 設定為 ON 時， 將此選項設定為 ON 才有效。預設情況下，AUTO_UPDATE_STATISTICS_ASYNC 選項設定為 OFF。

從以上說明中，我們可以看出，對於大表，還是有可能存在統計資訊更新不及時的時候，這時，就可能會影響查詢最佳化工具的判斷了。
有些人可能有個經驗：對於一些慢的查詢，他們會想到重建索引來嘗試解決。其實這樣做是有道理的。 因為，在某些時候一個查詢突然變慢了，可能和統計資訊更新不及時有關，進而會影響查詢最佳化工具的判斷。 如果此時重建索引，就可以讓查詢最佳化工具知道最新的資料分布，自然就可以避開這個問題。 還記得我前面用【set statistics profile on】顯示的執行過程表格嗎？注意哦，那個表格就顯示每個步驟的實際資料量和預估的資料量。要不要重建索引，其實我們可以用【set statistics profile on】來看一下，如果實際資料量和預估的資料量的差值比較大， 那麼我們可以考慮手工去更新統計資料，然後再去試試。

回到頂部最佳化視圖查詢

再來說說最佳化視圖查詢，雖然視圖也是由一個查詢語句定義的，本質上也是一個查詢，但它和一般的查詢語句在最佳化時，還是有所區別的。 這裡主要的區別在於，視圖雖然是由一個查詢語句定義的，但如果只去分析這個查詢定義，可能得到的意義不大，因為視圖多數時候就不是直接使用， 而是在使用前，會加上where語句，或者放在其它語句中供from子句所使用。下面還是舉個例子吧，在我的示範資料庫中有個視圖OrdersView，定義代碼前面有。 我們來看看，如果直接使用這個視圖，會有什麼樣的執行計畫出來：

從這個視圖可以看出，SQL Server會對錶Orders做全表掃描，應該是很低效的。再來看看下面這個查詢：

從這個執行計畫可以看出，與上面那個就不一樣了。前一個查詢中對Orders表的尋找是使用【Clustered Index Scan】的方式， 而現在在使用【Clustered Index Seek】的方式了，最右邊二個步驟的成本的百分比也發生了改變。這樣就足以說明，最佳化視圖時， 最好能根據實際需求，應用不同的過濾條件，再來決定如何去最佳化。

再來一個由三個查詢組成的情況來看看這個視圖的執行計畫。

select * from dbo.OrdersView where OrderId = 1;select * from dbo.OrdersView where CustomerId = 1;select * from dbo.OrdersView where OrderDate >= ‘2010-12-1‘ and OrderDate < ‘2011-12-1‘;

很明顯，對於同一個視圖，在不同的過濾條件下，執行計畫的差別很明顯。

SqlServer查詢計劃