了解圖形化執行計劃 -- 第3部分：分析執行計劃

英文原文：

<a href="http://www.sqlservercentral.com/articles/Execution+Plans/105810/" target="_blank">http://www.sqlservercentral.com/articles/Execution+Plans/105810/</a>

對于SQL Server資料庫管理者和開發來說，能夠了解和分析執行計劃是一項非常重要且有益的技能。執行計劃将查詢的預估花銷、索引使用和執行的操作文檔化輸出。所有的資訊對于試着加速一個慢查詢來說都是極其重要的。

這篇文章是關于圖形化執行計劃的三部分系列文章之一。第1部分解釋了執行計劃是什麼，并讨論了預估和實際執行計劃的不同。第2部分顯示了如何建立預估和實際執行計劃。最後，第3部分深入一個簡單的圖形化執行計劃，并讨論了一些最普遍的查詢中的操作。

閱讀執行計劃

讓我們看下如何從執行情況和執行計劃中獲得資訊。

基礎

圖1從[AdventureWorks2012].[Person].[Address]表上的一個簡單查詢顯示預估的執行計劃。如你所見，它由表明操作和關聯不同操作的箭頭的圖示組成。箭頭都指向左邊，表明執行計劃從右邊開始并向左運作。按時間順序閱讀執行計劃，從最右邊的操作開始并向左運作。箭頭的相對層次也表明了多少資料正被從一個操作傳遞到另一個。

<a href="http://s3.51cto.com/wyfs02/M02/79/A6/wKiom1aXSVij1Ux-AADBqUCLNP4419.jpg" target="_blank"></a>

圖1 一個簡單的預估執行計劃

在圖2中，頂部操作的箭頭比它下面的兩個操作更瘦。頂部操作是以也輸出更少的行。

<a href="http://s3.51cto.com/wyfs02/M00/79/A5/wKioL1aXSY3hlFTUAABqhCBuhoo481.jpg" target="_blank"></a>

圖2 一個稍微複雜的執行計劃

在建立了執行計劃後，就有地方可以看到查詢的總消耗。總消耗位于最後的操作。這就是最左邊圖示的上層，在這裡，就是Select操作。将滑鼠放在Select圖示的上面會給你一個關于操作資訊的提示框（圖3）。

我們對Estimated Subtree Cost感興趣。總的消耗是一個查詢是否執行很快的更好判斷的相對值。它由I/O消耗和CPU消耗組成。Operator Cost是I/O Cost和CPU Cost之和。在這裡我們的查詢很好的低于1。它有可能執行相對較快。另一方面，一個查詢總消耗上千可能需要相當長時間完成。一些因素影響着預估消耗。不僅是相關表和視圖的行和索引的數量，而且環境因素例如CPU數量和磁盤都被用于計算消耗。

<a href="http://s3.51cto.com/wyfs02/M00/79/A5/wKioL1aXSY7RtGjvAAB7UHqs_Wg445.jpg" target="_blank"></a>

圖3 檢視執行計劃總消耗

在檢查了總消耗并得到一個運作時間的相對感覺後，接下來，從右到左快速檢視執行的操作。在圖1，首先，有一個聚集索引掃描。一個掃描表名查詢不是非常可選擇性（看Frequent Plan Operations下的Index/Table Scan檢視更多細節）。在這個特定情況下，它是一個沒有where從句的産物。我們也有TOP操作，從查詢傳回前1000行。最後，有一個在select語句中傳回指定列的select操作。

一旦你有一個查詢正在做什麼的整體感受，我們将專注在最高消耗的操作上。在每個圖示的最下面對每個操作執行計劃列出了總消耗的百分比。從圖1到圖2，我們看到聚集索引掃描占比總消耗的99%。我們想專注在這個操作上。

我們看下這個圖示的提示框（看圖4），你會注意到有四個消耗列出。對于每個操作消耗分為CPU和IO消耗。Operator Cost是CPU和I/O Cost之和。Sub Tree Cost是目前的Operator Cost加上在它之前的操作的整個操作消耗。如果你從最右邊的操作開始并跟随箭頭向左，你會看到對每個完成的操作Sub Tree Cost累加。如我們之前檢視到的，在左邊最上層的操作的Sub Tree Cost包含了整個查詢的預估的消耗。

<a href="http://s3.51cto.com/wyfs02/M01/79/A5/wKioL1aXSZGww1dmAAGwKqnW9Dw649.jpg" target="_blank"></a>

圖4 執行計劃消耗

常見的執行計劃操作

下面是一些出現在查詢計劃裡的最常用的操作。

Index/Table Seek -- 當執行seek，SQL Server可以在索引裡有效查找特定值或值的一個可選擇範圍。使用圖書館類比，你使用圖書館的電腦查找一本書的位置，并獲得這本書的位置。

Index/Table Scan -- 在scan操作中，SQL Server通讀整個表或索引。再次使用圖書館類比，相當于查找圖書館中的每一本知道找到你想要的書。試着在大型的大學圖書館這麼做！Scan表明了搜尋标準沒有達到足夠的使用seek操作的選擇性。如果在列上沒有索引被查找或者傳回的值的數量在索引中占百分比很大（低選擇性），檢視所有的值會更有效。如果你感覺有指定一個WHERE從句或JOIN足夠保證使用索引，確定列上被索引。

Seek/Scan with Bmk謂詞 -- 當在一個索引查找或掃描的提示框的Seek Predicates部分檢視，你有時會看到一個謂詞像Bmknnnn這裡nnnn是數字。這表明SQL Server會建立一個書簽用于Index或RID Lookup。當書簽出現，index seek/scan是一個兩步過程的部分，優化器會在使用書簽建立了一個資料集後，執行聚集索引查找（或表查找，如果沒有聚集索引）。檢視關于Key/RID Lookup部分的更多資訊。

Joins

SQL Server使用了三種join操作：

Hash Match/Join -- Hash Match或者Join可以用于Join（Hash Join）和Group by的（Hash Match）。在這個操作中，查詢優化器從被關聯的兩個表（如圖形化執行計劃中所見）的上層表建構一個哈希表。這被稱為構造表。低層表（被稱為探測表）的每行然後搜尋構造表比對資料。在一個Group By的情況下，之前操作的結果被用于構造表和探測表。這類Join效率的關鍵是構造表的大小和伺服器的可用記憶體數量。如果足夠小，優化器将在記憶體中建立構造表。如果可能，Hash Match将會相當快。另一方面，如果構造表相當大，處理過程程式設計一個嵌套循環并且非常慢。當這發生的時候，Hash Match的消耗是執行計劃的重要的百分比，你應該使得查詢更具選擇性或者考慮增加一個索引。Hash Join和Match會在執行前等待有足夠的記憶體。

Merge Join -- 當上層表（如圖形化執行計劃中所示）在Join中很大，Merge Join将會是最快的Join類型。這個Join的消耗與兩個表的行數總和相關（#上層表的行數 + #下層表的行數）。Merge Join的效率的關鍵是關聯的兩個表必須在關聯列上已排序。如果表還沒有排序，優化器将會首先排序表。在這裡，你會在Merge Join操作器之前直接看到一個排序操作器。排序操作是非常消耗性能的，是以如果一個執行計劃在Merge Join之前顯示了一個有高消耗排序操作器，你可能想在關聯列添加索引。

Nested Loop -- 嵌套循環關聯的消耗和兩個表行數（#上層表的行數 * #下層表的行數）的乘積有關。雖然這個Join沒有Merge Join有效率，當表沒有排序時它的整體消耗比Merge Join低，當上層表很大時它的整體消耗比比Hash Join低。再則，如果操作的消耗在執行計劃中占很高百分比，那麼讓你的查詢更具選擇性或者添加一個索引。

Key/RID Lookup -- 當在一個非聚集索引上執行seek或scan時發生lookup，并且所有的資料不包含在索引中。當這種情況發生時，如果存在聚集索引，在聚集索引上發生Key Lookup，否則如果沒有聚集索引，執行行辨別符（RID）Lookup。Lookup是昂貴的操作，如果可能應該避免。為了消除Lookup，将Select語句中的列添加包含在非聚集索引中的include從句中，如果可能。要是你有很多列在Select語句中，不可能将所有都包含進去，又怎樣呢？接下來最應該做的事是使得非聚集索引比目前正使用的索引更具選擇性。那就是說，如果你不能移除Lookup，試着減少傳遞給Lookup的行數。

Compute Scalar -- 該操作執行計算處理一個單一值。通常是标量函數、算術計算或字元串聯接的結果。警告，優化器不能預估标量或多語句表值函數的執行計劃。當這些函數之一出現了性能低下，嘗試将它轉換為一個内聯表值函數。

Concatenation -- 對字元串聯接不操作；而是對于資料集合的聯接。最常用于在UNION ALL操作中國聯接資料。

Sort -- sort操作隻将非排序資料作為輸入并輸出一個排序集合。排序在大型資料集合上是非常消耗性能的。排序也需要在執行前等待記憶體足夠多。有高排序消耗的執行計劃應該被檢查用于優化。

Parallelism -- 如果SQL Server所在的機器上有不止一個處理器，可選的在多個處理器之間拆分操作。事實上，如果有多個處理器，優化器會建立兩個執行計劃，一個用于并行而一個沒有。SQL Server然後決定哪個最後可能消耗最短的時間。Parallelism緻力于将資料分割在多個處理器間覆寫操作并合并結果。如果并行操作消耗很高，你可以在查詢上使用MAXDOP查詢提示[OPTION (MAXDOP 1) ]強制使用一個處理器。

總結

在查詢優化中閱讀圖形化執行計劃是一個非常有用的技能。該系列文章介紹了這個主題。我歡迎你對後續文章的評論。如果你也想閱讀更多這個主題的内容，Grant Fritchey的書SQL Server執行計劃提供了最好的最廣泛的涵蓋内容。

參考

譯者補充：

淺談SQL Server中的三種實體連接配接操作

https://msdn.microsoft.com/zh-cn/library/dn144699.aspx

表值函數

表值函數提供強大的結果集生成能力。它可以在查詢内部表或視圖允許的任何地方使用。表值函數在使用上比傳回一個結果集的存儲過程更靈活，因為函數的結果集可以聯接到查詢中的其他表。

SQL Server中有兩種表值函數。内聯表值函數在概念上與帶參數的視圖類似。多語句表值函數允許多條語句在表變量中建立結果集來傳回。

1. 内聯表值函數

建立内聯表值函數很簡單。内聯表值函數的内容是一條帶參數的SELECT語句。傳回資料類型永遠是表，不過傳回表的結構由SELECT語句的結構來定義。下面是内聯表值函數的一個例子，檢索給定CustomerID的商品銷售總量。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

<code>USE AdventureWorks2008;</code>

<code>GO</code>

<code>CREATE</code> <code>FUNCTION</code> <code>Sales.ufnSalesByCustomer (@CustomerID </code><code>int</code><code>)</code>

<code>RETURNS</code> <code>TABLE</code>

<code>AS</code>

<code>RETURN</code>

<code>(</code>

<code>SELECT</code> <code>P.ProductID, P.</code><code>Name</code><code>, </code><code>SUM</code><code>(SD.LineTotal) </code><code>AS</code> <code>Total</code>

<code>FROM</code> <code>Production.Product </code><code>AS</code> <code>P</code>

<code>JOIN</code> <code>Sales.SalesOrderDetail </code><code>AS</code> <code>SD</code>

<code>ON</code> <code>SD.ProductID = P.ProductID</code>

<code>JOIN</code> <code>Sales.SalesOrderHeader </code><code>AS</code> <code>SH</code>

<code>ON</code> <code>SH.SalesOrderID = SD.SalesOrderID</code>

<code>WHERE</code> <code>SH.CustomerID = @CustomerID</code>

<code>GROUP</code> <code>BY</code> <code>P.ProductID, P.</code><code>Name</code>

<code>);</code>

注意，函數體由一條RETURN語句組成。使用這個函數的一個例子如下所示：

<code>SELECT</code> <code>* </code><code>FROM</code> <code>Sales.ufnSalesByCustomer(30052);</code>

内聯表值函數功能強大，在要求參數化查詢的情況下值得考慮。它們在結果集如何使用上提供更多的靈活性。

2. 多語句表值函數

多語句表值函數允許多條語句來建立表的内容。多語句表值函數可以用來替換使用多個步驟來建構結果集的存儲過程。

多語句表值函數允許開發人員使用多個步驟動态地填充表，這一點與存儲過程類似，不過它們可以在SELECT語句中像表那樣被引用。

使用多語句表值函數時，表的結構必須在函數頭定義。要為表使用一個變量名，并且所有修改資料的操作隻能引用表變量。

下面的例子是一個函數，類似上一節中建立的ufnSalesByCustomer。首先建立表變量，然後使用剛才建立的标量函數來更新表變量，讓它包含總的存貨清單。建立函數的語句如下所示：

18

19

20

21

<code>CREATE</code> <code>FUNCTION</code> <code>Sales.ufnSalesByCustomerMS (@CustomerID </code><code>int</code><code>)</code>

<code>RETURNS</code> <code>@</code><code>table</code> <code>TABLE</code>

<code>( ProductID </code><code>int</code> <code>PRIMARY</code> <code>KEY</code> <code>NOT</code> <code>NULL</code><code>,</code>

<code>ProductName nvarchar(50) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>TotalSales </code><code>numeric</code><code>(38,6) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>TotalInventory </code><code>int</code> <code>NOT</code> <code>NULL</code> <code>)</code>

<code>BEGIN</code>

<code>INSERT</code> <code>INTO</code> <code>@</code><code>table</code>

<code>SELECT</code> <code>P.ProductID, P.</code><code>Name</code><code>, </code><code>SUM</code><code>(SD.LineTotal) </code><code>AS</code> <code>Total, 0</code>

<code>JOIN</code> <code>Sales.SalesOrderDetail SD </code><code>ON</code> <code>SD.ProductID = P.ProductID</code>

<code>JOIN</code> <code>Sales.SalesOrderHeader SH </code><code>ON</code> <code>SH.SalesOrderID = SD.SalesOrderID</code>

<code>GROUP</code> <code>BY</code> <code>P.ProductID, P.</code><code>Name</code><code>;</code>

<code>UPDATE</code> <code>@</code><code>table</code>

<code>SET</code> <code>TotalInventory = dbo.ufnGetTotalInventoryStock(ProductID);</code>

<code>RETURN</code><code>;</code>

<code>END</code><code>;</code>

執行這個函數與執行前面的内聯函數一樣：

<code>SELECT</code> <code>* </code><code>FROM</code> <code>Sales. ufnSalesByCustomerMS (30052);</code>

-------------------------------------------------------------------------------------

表值函數和标量值函數的不同是 表值函數是傳回一個Table類型 Table類型相當與一張存儲在記憶體中的一張虛拟表.

本文轉自UltraSQL51CTO部落格，原文連結： http://blog.51cto.com/ultrasql/1735019，如需轉載請自行聯系原作者