SQL Server: Optimizing SQL Server Query Performance

發行項
08/17/2016

SQL Server

最佳化 SQL Server 查詢效能

Maciej Pilecki

摘要:

分析執行計畫
最佳化查詢
識別要微調的查詢

將資料庫伺服器最佳化不能少的就是微調個別查詢的效能。這跟微調伺服器安裝其他會影響效能的層面 (例如硬體和軟體組態) 一樣重要，甚至可能還要重要。

即使資料庫伺服器是在功能最強大的硬體上執行，它的效能還是可能因少數幾個不當的查詢而受到不良的影響。事實上，只要有一個錯誤的查詢 (又稱為「失控的查詢」)，就可能導致資料庫產生嚴重的效能問題。

反過來說，藉由微調一組佔用最多資源或最常執行的查詢，就能大幅提升資料庫的整體效能。我將在本文中探討一些技巧，讓您用來識別和微調伺服器上佔用最多資源和效能最差的查詢。

分析執行計畫

微調個別的查詢的第一步通常是了解查詢的執行計畫。執行計畫當中描述 SQL ServerTM 為了履行查詢和產生所要的結果集而執行的實體和邏輯作業順序。執行計畫是由一個稱為查詢最佳化工具的資料庫引擎元件，在處理查詢的最佳化階段內所產生的，這考量的因素有很多，例如查詢當中所用的搜尋述詞、牽涉的資料表及它們的聯結條件、傳回的資料行清單，以及是否有實用的索引可用作為資料的有效存取路徑等。

複雜的查詢可能有相當多的排列組合，因此查詢最佳化工具並不會評估所有的可能性，而是嘗試找出對於指定查詢來說「夠好」的計畫。這是因為尋找完美的計畫不一定永遠可行，即便可行，評估所有可能性來找出完美計畫的成本可能比所獲得的效能效益還高。從 DBA 的觀點看來，重要的是了解整個流程及當中的限制。

擷取查詢的執行計畫的方法有很多：

Management Studio 提供顯示實際執行計畫和顯示估計執行計畫等功能，這會以圖形的方式來呈現計畫。這些功能最適合用於直接檢視，而且是目前顯示和分析執行計畫最常使用的方法 (在本文中，我將採用以這種方式產生的圖形計畫來解說我的範例)。
不同的 SET 選項，如 SHOWPLAN_XML 和 SHOWPLAN_ALL，會以 XML 文件或資料集的形式傳回執行計畫，前者是使用特殊的結構描述來描述計畫，而後者則會針對執行計畫中的各項作業提供文字說明。
SQL Server Profiler 事件類別，例如 Showplan XML，可讓您針對追蹤所收集的陳述式來收集執行計畫。

雖然以 XML 的格式表示的執行計畫可能不容易閱讀，但這個選項倒是可讓您撰寫程序和公用程式來分析執行計畫，以尋找效能問題的症狀以及較不理想的計畫。XML 表示也可以存成 .sqlplan 副檔名的檔案，然後在 Management Studio 中開啟來產生圖形表示。您還可以將這些檔案儲存起來供稍後分析之用，這樣一來，就不用在每次想要進行分析時重新產生執行計畫。這在您想要比較計畫來查看它們隨著時間的變動時尤其好用。

執行的估計成本

關於執行計畫，您必知的第一件事是它們是怎麼產生的。SQL Server 是採用以成本為基礎的查詢最佳化工具 — 也就是說，它會試圖產生估計成本最低的執行計畫。估計值是趁著最佳化工具評估查詢中所牽涉的每個資料表時，根據提供給最佳化工具的資料散佈統計資料得來的。如果漏掉該些統計資料，或是統計資料過時，查詢最佳化工具就缺乏重要的資訊來進行查詢最佳化程序，因而可能產生錯誤的估計值。在這種情況下，最佳化工具會高估或低估不同計畫的執行成本，而選擇不盡理想的計畫。

關於估計的執行成本有些常見的錯誤假設，特別是，人們往往假設估計的執行成本能夠清楚指出查詢要花多少時間執行，以為這樣的估計可讓您判別計畫是好是壞。這是錯誤的想法，首先，估計成本是用什麼單位來表示，還有它們與執行的時間有沒有直接的關聯都記載得很清楚。其次，既然這是估計，就表示並不一定正確，因此就 CPU、I/O 和執行時間而言，具有較高估計成本的計畫雖然有較高的估計值，有時候反而比較有效率。這種情況常常發生在牽涉資料表變數的查詢 — 因為資料表變數沒有可用的統計資料，所以即使資料表變數包含許多資料列，查詢最佳化工具總是假設資料表變數只包含一個資料列。結果，查詢最佳化工具將根據錯誤的估計來選擇計畫。因此，當比較查詢的執行計畫時，不應單獨仰賴估計的查詢成本。反而應將 STATISTICS I/O 和 STATISTICS TIME 選項的輸出包含在分析當中，以利了解 I/O 和 CPU 時間上的實際執行成本為何。

這裡有個特殊的執行計畫特別值得一提，它叫做平行計畫。如果您在含有一個 CPU 以上的伺服器上執行查詢，且該查詢可進行平行處理，那麼就可選擇平行計畫 (一般來說，查詢最佳化工具只會在查詢的成本超過特定可設定的閾值時才會考慮採納平行計畫)。因為管理多個執行的平行執行緒 (表示將工作分攤給不同執行緒，執行同步處理，以及收集結果) 會有額外的負荷，所以平行計畫的執行成本較高，而且這會反映在它們的估計成本上。那麼為什麼不選擇較便宜的非平行計畫呢？這都要拜使用多 CPU 的處理威力所賜，平行計畫產生結果的速度比標準計畫要來得快。視您特定的案例而定，將這類變數包括作為可用資源以及其他查詢的同時負載，這種計畫可能蠻適合您的情況。若是如此，您應該控制哪些查詢可以產生平行計畫，還有每個查詢可利用多少 CPU。要這麼做，您可以在伺服器層級上設定最大程度的平行處理選項，並在必要時在個別的查詢層級以 OPTION (MAXDOP n) 將它覆寫。

分析執行計畫

我現在要來探討一個簡單的查詢，包括它的執行計畫，還有提升它的效能的一些方法。假設我是使用 Management Studio 來執行此查詢，並在 SQL Server 2005 上的 Adventure Works 範例資料庫中開啟了 [包含實際執行計畫] 選項。

SELECT c.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID
GROUP BY c.CustomerID

結果，我看到 [圖 1] 中所述的執行計畫。這個範例查詢會計算 Adventure Works 每位客戶所下的訂單總數。看一下執行計畫，便可以看出資料庫引擎是怎麼處理查詢和產生結果的。圖形執行計畫的閱讀方式是從上到下，從右到左。每個圖示都表示一項已執行的邏輯和實體作業，而箭號顯示出作業之間的資料流向。箭號的厚度表示作業之間傳遞的資料列數量 — 箭號越厚，表示牽涉的資料列越多。如果您將指標放到其中一個運算子圖示上，會有一個黃色的工具提示 (如 [圖 2] 中所示) 顯示該特定作業的詳細資料。

[圖 1] 範例執行計畫

[圖 1]** 範例執行計畫 **(按影像可放大)

[圖 2] 作業的相關詳細資料

[圖 2]** 作業的相關詳細資料 **(按影像可放大)

看看每個運算子，便可分析執行的步驟順序：

資料庫引擎在 Sales Customer 資料表上執行叢集索引掃描作業，然後傳回該資料表中所有資料列的 CustomerID 資料行。
它接著對 Sales.SalesOrderHeader 資料表中的其中一個索引執行索引掃描 (意指非叢集索引掃描)。這是 CustomerID 資料行上的索引，但它也隱含地包含了 SalesOrderID 資料行 (即資料表叢集索引鍵)。這兩個資料行上的值是掃描傳回的。
兩次掃描的輸出已使用「合併聯結」實體運算子聯結在 CustomerID 資料行上 (這是三種實際執行邏輯聯結作業可能的方法之一)。它的速度很快，但是要求兩邊的輸入都要在聯結的資料行上排序。在本例中，兩次掃描作業都已傳回 CustomerID 上排序的資料行，因此不需要執行額外的排序作業)。
接下來，資料庫引擎在 Sales.SalesOrderDetail 資料表上執行一次叢集索引掃描，從此資料表中的所有資料列擷取四個資料行 (SalesOrderID、OrderQty、UnitPrice 和 UnitPriceDiscount) 的值 (此作業估計會傳回 123,317 個資料列，您可從 [圖 2] 中的 [估計的資料列數目] 和 [實際的資料列數目] 屬性，看出實際上也傳回了此數目 — 因此估計非常正確)。
叢集索引掃描所產生的資料列會傳遞給第一個「計算純量」運算子，讓每個資料列可以根據公式中所包含的 OrderQty、UnitPrice 和 UnitPriceDiscount 資料行計算資料行 LineTotal 的值。
第二個「計算純量」運算子會依照計算資料行公式的要求，將 ISNULL 函數套用到前次計算的結果中。這便完成了 LineTotal 資料行的計算，並將它連同 SalesOrderID 資料行傳回給下一個運算子。
步驟 3 中「合併聯結」運算子的輸出會使用「雜湊比對」實體運算子，與步驟 6 的「計算純量」運算子的輸出聯結起來。
另外一個「雜湊比對」運算子接著會套用到從合併聯結由 CustomerID 資料行值傳回的資料列群組，並計算 LineTotal 資料行的 SUM 彙總。
最後一個節點 SELECT，既非實體亦非邏輯運算子，而是代表整體查詢結果和成本的預留位置。

在我的膝上型電腦上，這個執行計畫的估計成本是 3,31365 (如 [圖 3] 所示)。當在 STATISTICS I/O 處於 ON 的狀態執行時，查詢報告得出在三個牽涉的資料表間執行了 1,388 項邏輯讀取作業。每個作業下方顯示的百分比表示每個個別運算子的成本與整個執行計畫的整體估計成本的比數。看一下 [圖 1] 中的計畫，可以看出整個執行計畫的總成本大多與下列三個運算子有關：Sales.SalesOrderDetail 資料表的叢集索引掃描，以及兩個雜湊比對運算子。但是在繼續進行最佳化之前，我想要指出我在查詢當中做了一項非常簡單的變更，可讓我同時剔除兩個運算子。

[圖 3] 查詢的總估計執行成本

[圖 3]** 查詢的總估計執行成本 **

既然我從 Sales.Customer 資料表傳回的唯一一個資料行是 CustomerID，而此資料行也包含在 Sales.SalesOrderHeaderTable 中的外部索引鍵中，因此只要使用這段程式碼，不需要變更查詢所產生的邏輯意義或結果，便可完全將 Customer 資料表從查詢中剔除：

SELECT oh.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

這便產生不同的執行計畫，如 [圖 4] 所示。

[圖 4] 從查詢中剔除 Customer 資料表之後的執行計畫

[圖 4]** 從查詢中剔除 Customer 資料表之後的執行計畫 **(按影像可放大)

兩項作業完全剔除掉 — Customer 資料表上的叢集索引掃描，以及 Customer 與 SalesOrderHeader 之間的合併聯結 — 而雜湊比對聯結則被效率高出許多的合併聯結所取代。不過，為了在 SalesOrderHeader 和 SalesOrderDetail 資料表之間使用合併聯結，兩個資料表的資料列都必須由 SalesOrderID 聯結資料行經過排序傳回。為了達到這個目的，查詢最佳化工具決定不使用非叢集索引掃描，而是在 SalesOrderHeader 資料表上執行叢集索引掃描，因為就牽涉的 I/O 而言，它的成本比較低。這是示範查詢最佳化工具實際運作的理想範例 — 既然變更執行聯結作業的實際方法所省下的成本比叢集索引掃描所產生的額外 I/O 還要高，因此查詢最佳化工具便選擇所得的運算子組合，因為它所給的總估計執行成本最低。在我的膝上型電腦上，即使邏輯的讀數很高 (達 1,941)，但耗用的 CPU 時間其實很短，而此查詢的估計執行成本也降低了 13% (2,89548)。

假設我還想要更進一步提升此查詢的效能，我現在看到 SalesOrderHeader 資料表的叢集索引掃描變成此執行計畫中最昂貴的運算子。既然在此資料表中只需要兩個資料行便可完成查詢，我可以建立一個只包含這兩個資料行的非叢集索引 — 以較小許多的非叢集索引掃描來取代掃描整個資料表。索引定義看起來可能像這樣：

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine
ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

請注意，我建立的索引包含了一個計算資料行。這不一定都能成立，要視計算資料行的定義而定。

建立好此索引然後執行相同的查詢之後，我得到新的執行計畫，如 [圖 5] 所示。

[圖 5] 最佳化後的執行計畫

[圖 5]** 最佳化後的執行計畫 **(按影像可放大)

SalesOrderDetail 資料表上的叢集索引掃描已由 I/O 成本明顯低很多的非叢集索引掃描取代。我也剔除了其中一個計算純量運算子，因為我的索引包含的 LineTotal 資料行已經計算出值。估計的執行計畫成本現在是 2,28112，而查詢在執行時進行了 1,125 次邏輯讀取作業。

涵蓋索引

客戶訂單查詢練習

問：下面是客戶訂單查詢練習：請試著找出索引定義 — 要成為此查詢的涵蓋索引應該包含哪些資料行，還有索引定義內的資料行順序對效能是否有影響。

答：我提出挑戰要您找出針對本文中的範例查詢在 Sales.SalesOrderHeader 資料表上建立的最佳涵蓋索引。要接受挑戰，您應該要注意的第一件事是查詢只使用資料表的兩個資料行：CustomerID 和 SalesOrderID。如果您仔細地閱讀本文，應該會注意到在 SalesOrderHeader 資料表中，已經有一個現有的索引涵蓋此查詢 — 那就是 CustomerID 上的索引，而且它隱含也包含了 SalesOrderID 資料行，此資料行是資料表的叢集索引鍵。

當然，我也解釋了為什麼查詢最佳化工具決定不使用此索引。沒錯，您是可以強制查詢最佳化工具使用這個索引，但是這樣的解決方案比採用叢集索引掃描和合併聯結運算子的現有計畫還沒效率。這是因為您等於是強迫查詢最佳化工具選擇是要在另外執行排序作業以便繼續使用合併聯結，還是回頭使用效率較低的雜湊聯結。兩種選項的估計執行成本都比現有計畫要高 (進行排序作業的那個版本的效能特別差)，因此除非強制查詢最佳化工具，否則它不會使用這兩種選項。所以說，在這種情況下，唯一一個比叢集索引掃描執行要佳的索引是 SalesOrderID、CustomerID 上的非叢集索引。但要特別注意的是，資料行必須遵守下列順序：

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)

如果您建立這個索引，則執行計畫將包含索引掃描，而不是叢集索引掃描運算子，這可是南轅北轍。在本例中，只包含兩個資料行的非叢集索引遠比以叢集索引的形式表示的整個資料表要小得多。因此，它讀取必要資料所需的 I/O 也比較少。

這個範例也說明了索引內的資料行順序深深左右著它對於查詢最佳化工具的有用程度。在設計多資料行索引時，要特別記住這點。

我在 SalesOrderDetail 上建立的索引是所謂的「涵蓋索引」。它是一種非叢集索引，包含了完成查詢所需的所有資料行，因而不需要使用資料表掃描或叢集索引掃描運算子來掃描整個資料表。這個索引基本上是資料表的小型副本，當中包含了資料表的資料行子集。只有那些需要回答查詢的資料行才會包含在索引中 — 換句話說，索引只包含「涵蓋」查詢所需的內容。

為最常執行的查詢建立涵蓋索引是用於查詢微調其中一個最簡單也是最常見的方法。這種方法特別適用於當資料表包含大量資料行，但查詢最常參考的只有當中少數的情況。您可以透過建立一或多個涵蓋索引，大幅提升受影響查詢的效能，因為它們存取的資料量小很多，轉而產生較低的 I/O。不過，在資料修改作業 (INSERT、UPDATE 和 DELETE) 期間維護額外的索引倒是得付出不易察覺的成本。視您的環境和 SELECT 查詢與資料修改之間的比率而定，您應該審慎判斷此額外的索引維護負荷與查詢效能提升比起來，是否值得。

別只建立單一資料行索引，勇敢試著建立多資料行索引。多資料行索引比單一資料行索引要有用得多，而且查詢最佳化工具也比較可能會用它們來涵蓋查詢。大多數的涵蓋索引都是多資料行索引。

我的範例查詢還是有改進的空間，在 SalesOrderHeader 資料表上放一個涵蓋索引可進一步最佳化這個查詢。此舉可消除叢集索引掃描，而採用非叢集索引掃描。這就留給您自己練習，試著找出索引定義 — 要成為此查詢的涵蓋索引的應該包含哪些資料行，還有索引定義內的資料行順序對效能是否有影響。答案請參閱「客戶訂單查詢練習」資料看板。

索引檢視

如果效能對於我的範例查詢來說很重要，我還可以進一步建立一個索引檢視，實際儲存查詢的具體結果。索引檢視有些先決條件和限制，但若可以採用，它肯定能大幅提升效能。要記住索引檢視的維護成本比標準索引還高。因此，理當謹慎使用。在本例中，索引定義看起來像這樣：

CREATE VIEW vTotalCustomerOrders
WITH SCHEMABINDING
AS
SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

請注意 WITH SCHEMABINDING 選項和 COUNT_BIG(*) 函數，前者是在這類檢視上建立索引的必備選項，而若是索引定義包含彙總函數 (在本例中，即 SUM) 則需要用到後者。建立此檢視之後，我便可以在它上面建立索引，像這樣：

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID 
ON vTotalCustomerOrders(CustomerID)

我在建立這個索引時，包含在檢視定義內的查詢結果會具體化，而且實際上會儲存在索引內的磁碟上。要注意的是，在基底資料表上進行的所有資料修改作業接著便會根據其定義自動更新檢視中的值。

如果我現在傳回查詢，接下來會發生什麼事完全要看我執行的 SQL Server 版本而定。在 Enterprise 或 Developer 版本，查詢最佳化工具會自動將此查詢與索引檢視定義相比對，然後使用索引檢視，而不是查詢所包含的基底資料表。[圖 6] 顯示此例中所產生的執行範例。當中只包含一項作業 — 即我在檢視上建立的索引的叢集索引掃描。估計的執行成本只有 0,09023，而它只執行了 92 項邏輯讀取作業。

[圖 6] 使用索引檢視時的執行計畫

[圖 6]** 使用索引檢視時的執行計畫 **(按影像可放大)

您還是可以在其他版本的 SQL Server 上建立和使用這個索引檢視，但是要達成相同的效果，您得修改查詢，使用 NOEXPAND 提示直接參考檢視，像這樣：

SELECT CustomerID, OrdersTotalAmt
FROM vTotalCustomerOrders WITH (NOEXPAND)

如您所見，如果使用得當，索引檢視可說是相當強大的功能。它們在最佳化對大量資料執行彙總的查詢時特別實用。如果用在 Enterprise Edition 上，則不需要變更程式碼，就能使許多查詢受益。

識別要微調的查詢

我要如何識別值得微調的查詢呢？我會找最常執行的查詢 — 它們單次執行的成本可能不高，但是彙總起來的執行成本可能比鮮少執行的大型查詢要高許多。我不是說您不應該微調大型查詢，而是覺得您應該先把焦點放在最常執行的查詢。那麼您要如何識別它們呢？

可惜，最可靠的方法有點複雜，而且動作包括對伺服器追蹤所有已執行的查詢，然後依它們的簽章來分組 (也就是說，以預留位置取代實際參數值來查詢文字以便識別相同的查詢類型，即使它已使用不同的參數值執行也一樣)。這是相當複雜的程序，因為要產生查詢簽章很難。Itzik Ben-Gan 在他《Inside Microsoft SQL Server 2005:T-SQL Querying》的書中提出了一個解決方案，就是使用 CLR 使用者定義的函數和規則運算式。

另外有一個簡單許多但比較不可靠的方法。您可以仰賴在執行計畫中所保留的所有查詢統計資料，然後使用動態管理檢視來查詢它們。[圖 7] 中包含了一個範例查詢，顯示快取中邏輯讀取累加數字最高的 20 個查詢的文字和執行計畫。這個查詢對於快速識別產生最高邏輯讀數的查詢非常好用 — 但它也有限制。那就是，它只會顯示您在執行查詢時有快取它們的計畫的查詢。如果有內容沒有快取到，就會遺漏掉。

Figure 7 識別前 20 個在讀取 I/O 時耗用最多資源的查詢

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

識別出那些效能低劣的查詢後，可以看看它們的查詢計畫，並使用我在本文中說明的一些索引技巧來尋找提升其效能的方法。如果成功了，您下的功夫肯定不會白費。

祝您微調愉快！

Maciej Pilecki 在 Solid Quality Mentors 擔任副顧問一職，這是一家專營教育訓練、顧問和諮詢的國際組織。他身兼微軟認證講師 (Microsoft Certified Trainer，MCT) 和 SQL Server 最有價值專家 (Most Valuable Professional，MVP)，經常講授有關 SQL Server 和應用程式開發許多方面的課程和研討會。

其他資源