本節方法適用於 SQL Server Management Studio 2005 及之後的版本。

取得執行計畫#

SQL Server 有數種取得執行計畫的方式，其中最重要的兩種：

  • 圖形化：在 Management Studio 中很容易取得，但難以分享——述詞資訊只有把滑鼠移到特定操作上（hover）時才看得到。
  • 表格式：閱讀較困難，但容易複製，因為它一次呈現所有相關資訊。

圖形化#

用工具列上的兩個按鈕之一產生：左邊的按鈕直接解釋當前選取的敘述，右邊的則會在下一次執行 SQL 時擷取計畫。兩者的圖形結果都出現在「Results」窗格的「Execution plan」分頁。

圖形表示法稍加練習後很好讀，但它只顯示最基本的資訊：操作，以及它作用的資料表或索引。把滑鼠移到操作上時 Management Studio 才顯示更多資訊——這也使得完整分享執行計畫變得困難。

表格式#

表格式的執行計畫透過剖析（profiling）敘述執行來取得：

SET STATISTICS PROFILE ON

啟用後，每個執行的敘述都會多產生一個結果集。例如 select 會產生兩個結果集——先是敘述本身的結果，接著是執行計畫。

表格式計畫在 SQL Server Management Studio 中幾乎不堪使用，因為 StmtText 欄位太寬、放不進螢幕。

它的優勢在於複製時不會遺失相關資訊——想把 SQL Server 執行計畫貼到論壇之類的平台時非常方便。通常只要複製 StmtText 欄位並稍加排版即可：

select COUNT(*) from employees;
  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(...))
       |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
            |--Index Scan(OBJECT:([employees].[employees_pk]))

最後再關閉剖析：

SET STATISTICS PROFILE OFF

操作#

索引與資料表存取#

SQL Server 的術語很簡單：「Scan」操作讀取整個索引或資料表，「Seek」操作則用 B-tree 或實體位址（RID，類似 Oracle 的 ROWID）存取索引或表的特定部分。

  • Index Seek：做 B-tree 走訪並走過葉節點找出所有匹配條目。
  • Index Scan：依索引順序讀取整個索引（所有列）。當需要「依索引順序取得所有列」時（例如對應的 order by）可能採用。
  • Key Lookup (Clustered)：從叢集索引取回單一列，類似 Oracle 對索引組織表（IOT）的 INDEX UNIQUE SCAN。
  • RID Lookup (Heap)：從資料表取回單一列，相當於 Oracle 的 TABLE ACCESS BY INDEX ROWID。
  • Table Scan：即全表掃描，以磁碟儲存順序讀取整張表。雖然多區塊讀取能大幅提升速度，它仍是最昂貴的操作之一——除了高 IO，還必須檢視所有列，消耗可觀的 CPU 時間。

Join#

join 一次只處理兩張表；查詢中有更多 join 時依序執行。因此「表」也可能意指「中間結果」。

  • Nested Loops：從一張表取得結果，再為每一列查詢另一張表。SQL Server 也用這個操作在索引存取後取回資料表資料。
  • Hash Match：把 join 一側的候選記錄載入雜湊表，再以另一側的每一列探測。
  • Merge Join：像拉鍊一樣合併兩份已排序的清單，兩側都必須預先排序。

排序與分組#

  • Sort：依 order by 排序結果。需要大量記憶體具體化中間結果（非管線化）。
  • Sort (Top N Sort)：依 order by 排序結果的一個子集。用於無法管線化執行的 top-N 查詢。
  • Stream Aggregate：對已排序的集合依 group by 彙總。不緩衝中間結果，以管線化方式執行。
  • Hash Match (Aggregate)：用雜湊表分組。需要大量記憶體具體化中間結果（非管線化），且輸出沒有任何有意義的排序。

Top-N 查詢#

  • Top：抓到所需列數時中止底層操作。

Top-N 查詢的效率取決於底層操作的執行模式——中止 Sort 這類非管線化操作時效率極差。

區分存取述詞與篩選述詞#

SQL Server 同樣以三種方式套用 where 子句：

  • 存取述詞（Seek Predicates）：葉節點走訪的起訖條件。
  • 索引篩選述詞（索引操作上的 Predicates 或 where）：只在葉節點走訪期間套用，不縮小掃描範圍。
  • 資料表層級篩選述詞（資料表操作上的 where）：欄位不屬於索引，必須先從堆積表載入該列才能求值。

以第 3 章示範索引篩選述詞影響的範例為基礎：

CREATE TABLE scale_data (
   section NUMERIC NOT NULL,
   id1     NUMERIC NOT NULL,
   id2     NUMERIC NOT NULL
);
CREATE INDEX scale_slow ON scale_data(section, id1, id2);

SELECT count(*)
  FROM scale_data
 WHERE section = @sec
   AND id2 = @id2

在圖形化計畫中#

圖形化計畫把述詞資訊藏在工具提示裡，只有把滑鼠移到 Index Seek 操作上才會顯示。

SQL Server 的 Seek Predicates 對應 Oracle 的存取述詞——它們縮小葉節點走訪範圍。篩選述詞在圖形化計畫中則只標示為 Predicates。

在表格式計畫中#

表格式計畫把述詞資訊放在與操作同一欄，因此很容易一次複製所有相關資訊：

DECLARE @sec numeric;
DECLARE @id2 numeric;
SET STATISTICS PROFILE ON

SELECT count(*)
  FROM scale_data
 WHERE section = @sec
   AND id2 = @id2

SET STATISTICS PROFILE OFF

執行計畫作為第二個結果集出現。以下是稍加排版的 StmtText 欄位：

|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(...))
     |--Stream Aggregate(DEFINE:([Expr1008]=Count(*)))
          |--Index Seek(OBJECT:([scale_data].[scale_slow]),
             SEEK: ([scale_data].[section]=[@sec])
                    ORDERED FORWARD
             WHERE:([scale_data].[id2]=[@id2]))
Backward PostgreSQL MySQL Forward