2011年6月9日 星期四

如何解讀 SQL Server 的圖型式執行計畫

本篇主要是從MSSQL Tips的網站轉載而來,主要介紹如何解讀一個圖型式的執行計畫,另外在圖示的部份,我另外從 Microsoft SQL Server 的網站中整理出來,我想解讀執行計畫應該是每一個 DBA 所需要具備的能力,所以特別在此介紹給大家。

問題:
在先前的章節中,作者有提到如何簡單的了解一個圖型化介面的查詢計畫。我們有機會深度的了解圖型式執行計畫的資訊來源。是的,你看的沒錯。但仍有其他的訊息,並無法直覺化的看出,需要進一步的解讀,如工具上的提示和屬性視窗的說明等。

方法:
在過去兩年間,作者不斷的提出與微軟產品相關的文章。意思上微軟作為一個軟件開發公司花了很多時間確保他們的產品也有類似的圖形用戶界面(GUI)設計和行為可以提供給使用者方便使用。

經常使用Microsoft產品的使用者,應該都知道無論是使用ExcelSQL Server Management Studio 其操作介面都會大致相同,都可以透過滑鼠右鍵進行進一步的資訊選擇。或是透過滑鼠移動到圖表上時,也是相同可以秀出額外的資訊。

類似其他的微軟產品,SQL Server Management Studio 也同樣有工具上的提示。圖型化查詢計畫將讓他提升到一個不同的等級,透過豐富的工具提示讓你看得更多。

記得最先的查詢和圖型式的執行計畫,作者已有表達在先前的作品集中?如果沒有,當下再我們再來看一次。作者繼續去使用他如同我們之前討論的基礎。這是一個非常簡單的 SELECT 語法在SQL Server 2005 - Northwind 的樣本資料庫之中,語法中包含一個過濾與排序操作。


SELECT [CustomerID], [CompanyName], [City], [Region]
FROM [Northwind].[dbo].[Customers]
WHERE [Country] = 'Germany'
ORDER BY [CompanyName]




在上面評估的執行計畫中,作者透過1到5的數字去幫助進行下面的解譯。

接著來看各個操作步驟的提示在這個簡單的執行計畫中,在閱讀方法上執行計畫本身從左到右進行參考。你將看到相似的操作提示,但是你應該注意到當你將箭頭移動到資料流與操作之間時,都會秀出提示讓你了解的更深入。

所以,如果你在各個項目的執行計劃中,你將取得不同的資訊,下列將秀出各自五個編號的資訊集合在這個執行計劃中。


1 - Clustered Index Scan Operator




作者將花一些時間去檢示每一個項目來說明,到目前為止我們已經看到第一個提示的部份,在此作者將只關注新的或不同的線性項目在每一個操作中的子項目提示。你會看到提示顯示的部份以及最初的操作標準化描述。接著經由評估操作程序 (在這個執行計畫的評估中)。如果這是一個真實的執行計畫,你會看到的實際數目也行參與的運作後的物理和邏輯運算的操作指標。

  • Physical Operation - 所用的實體運算子,如「雜湊聯結」或「巢狀迴圈」。實體運算子若以紅色顯示,表示查詢最佳化工具已發出警告,如遺漏資料行統計資料,或遺漏聯結述詞。這可能導致查詢最佳化工具出人意外地選擇效能低的查詢執行計畫。如需有關資料行統計資料的詳細資訊,請參閱<使用統計資料來改善查詢效能>。
    當圖形執行計畫建議建立或更新統計資料或建立索引時,就可以使用 SQL Server Management Studio 之 [物件總管] 的捷徑功能表,立即建立或更新遺漏的資料行統計資料與索引。如需詳細資訊,請參閱<索引的如何主題>。
  • Logical Operation - 符合實體運算子的邏輯運算子,如「內部聯結」運算子。邏輯運算子會列在位於在「工具提示」頂端的實體運算子後面。
  • Estimated I/O Cost - 這個值用來評估I/O操作的密集度。
  • Estimated CPU Cost - 該作業之所有 CPU 活動的估計成本。
  • Estimated Operator Cost - 查詢最佳化工具執行此作業的查詢成本。此作業的成本會當作查詢總成本的百分比顯示在括號內。因為查詢引擎會選擇最有效率的作業來執行查詢或執行陳述式,所以這個值應該越低越好。
  • Estimated Subtree Cost - 查詢最佳化工具執行此作業與同一子樹中此作業前面之所有作業的總成本。
  • Estimated Number of Rows - 由運算子產生的資料列數目。
  • Estimated Row Size - 由運算子產生的估計資料列大小 (位元組)。
  • Ordered - 一個布林值,用來指定運算子是否有將每一列進行排序。
  • NodeID - 查詢的執行計畫中具體的序號值。
接著在下半部的部份,分別另外有三個欄位的運算元分別為Predicate、Object 和 Output 。
  1. Predicate是一個術語,用來描述查詢中的過濾、描述或比較資料。在這個案例中,主要顯示查詢篩選結果,其中國家別的部份只顯示有興趣的國家別為 'Germany' 的資料行。
  2. Object用來描述這個執行計畫中Customers 的表格使用那一個主鍵值。
  3. Output主要顯示此次的語法中,會顯示那些欄位。


2- 資料流箭頭: Clustered Index Scan Operator to Sort Operator



你能分辯的出目前顯示出的是實際或估計的圖形式執行計劃嗎?他可能不是如你預期的這麼簡單。在圖型執行計畫中有兩種類型可以提示看出。無論如何,真實的查詢計畫將包含在Actual Number of Rows之中。


工具提示相關的數據流箭頭很簡單,他提供資訊去評估(或是真實的)資料移動在查詢的運算子之中。


3 -Sort Operator


在Sort operator 和 Clustered Index Scan operator在補捉的工具提示是相同的。顯然地,這些的值是不同的。你將可以看到評估子樹的成本增加與前面的操作。



4 - Data Flow Arrow: Sort Operator to SELECT Operator


我們在下一個遇到的資料流箭頭之間的排序和SELECT操作仍可能會有相同的內容(但並不一定有相同的值)。而且是幾乎同時的完成!



5- SELECT Operator



注意,在SELECT Operator's 的提示是大大不同於其他項目的提示如我們所見 - 在DML中將同樣有不同的比較到其他的操作項目,我們可以到 SELECT operator's 的操作方式。當然也可以看到其他的DML操作提示在這個項目中進行討論。在這個案例中,SELECT operator's 有一個新的項目為 Cached plan size。這個項目主要是指查詢計畫花了多少的cache進行處理。這個數值將可幫助你去了解 memorycache 的效能表現。

Microsoft SQL Server 的圖型式執行計畫提供了許多的資訊可以讓你了解到其中的操作方式,當然在此工具上也有一些無法直覺式看出的資訊,然而在本篇中也有介紹到如何進行觀察,而這此資訊也提供給我們在撰寫T-SQL時進行最佳化的調整,藉以讓執行計畫更有效率。



補充說明:圖型執行計畫圖示說明

下列圖示顯示在圖形執行計畫中,代表 SQL Server 用來執行陳述式的<資料指標邏輯與實體 Showplan 運算子>。
下列圖示顯示在圖形執行計畫中,代表 SQL Server 用來執行陳述式的Parallelism Showplan 運算子實體運算子。
圖示平行處理原則實體運算子
Distribute streams parallelism operator icon散發資料流
Repartition streams parallelism operator icon重新分割資料流
Gather streams parallelism operator icon收集資料流
下列圖示顯示在圖形執行計畫中,代表 SQL Server 使用的 Transact-SQL 語言項目。


參考網址:

  1. http://www.mssqltips.com/tip.asp?tip=1873
  2. http://msdn.microsoft.com/en-us/library/ms178071.aspx
  3. http://msdn.microsoft.com/zh-tw/library/ms175913.aspx

8 則留言:

  1. 好文章,對 Programmer 很有幫助!

    回覆刪除
  2. hi~您好

    最近在學SQL, 偶然發現了您的文章,
    想請教一下,
    假設我有兩個SQL, 是要做相同的事情,
    實際執行起來, 感覺不到差異,
    那是否可以執行計畫最左邊的節點之子樹成本判別其優劣呢?

    thanks

    回覆刪除
    回覆
    1. Hi 你好:

      你從子樹上應該也可以看到這個子樹的執行成本,所以還是可以看到,而且透過執行計畫應該你也可以點開這個子樹的部份,藉以參考,如果仍有不清楚的部份,歡迎再討論,謝謝。

      刪除
  3. 讀了你的文章,受益良多。

    想請教一個問題,Execution plan上面所顯示的很多項目都是Estimated Cost, 例如CPU, IO, Subtree

    他顯示的是Estimated,那會不會實際執行的時候,會有所差異?

    (即使我選擇Actual Execution plan,很多項目仍是顯示Estimated Cost)

    謝謝

    回覆刪除
    回覆
    1. 關於兩者的差異說明如下,關於兩者之間我也是有遇過實際與評估的不同的情況,但其實不會相差太多的。

      Estimated and Actual Execution Plans

      As discussed previously, there are two distinct types of execution plan. First, there is the plan that represents the output from the optimizer. This is known as an Estimated execution plan. The operators, or steps, within the plan will be labeled as logical, because they're representative of the optimizer's view of the plan.

      Next is the plan that represents the output from the actual query execution. This type of plan is known, funnily enough, as the Actual execution plan. It shows what actually happened when the query executed.

      刪除