導讀：
最佳化資料庫伺服器時，您需要最佳化個人查詢效能。這與最佳化伺服器安裝的其他影響硬體和軟體配置等效能的方面一樣重要 — 甚至更為重要。
即使資料庫伺服器是在功能最強大的硬體上運行，它的效能還是可能會因為一些行為錯誤的查詢而受到負面影響。實際上，只要有一個壞的查詢（有時稱為“失控查詢”），就可能導致資料庫出現嚴重的效能問題。
反之，通過最佳化一組成本最高或最常執行的查詢，可以極大地提高資料庫的整體效能。在本文中，我將探討一些技術，您可使用這些技術識別並最佳化伺服器上成本最高和效能最壞的查詢。
分析執行計畫
最佳化個人查詢時，通常通過查看查詢的執行計畫開始。此執行計畫描述 SQL ServerTM為滿足查詢和產生所需結果集而執行的物理和邏輯操作的順序。此執行計畫是在查詢處理的最佳化階段由稱為查詢最佳化工具的資料庫引擎組件產生的，這要考慮到許多不同的因素，例如，查詢中使用的搜尋謂詞、涉及的表和其串連條件、返回的列清單，以及是否存在能用作資料的有效訪問路徑的有用索引。
對於複雜的查詢，所有可能排列的數量可能非常大，因此查詢最佳化工具不評估所有的可能性，而是嘗試尋找對於給定查詢“足夠好”的計劃。這是因為並不總能尋找完美計劃；即便可能，評估所有可能性以尋找完美計劃的開銷與所獲得的效能改進相比起來，也很可能會得不償失。從 DBA觀點來看，瞭解其過程和局限性很重要。
有許多方法可用於檢索查詢的執行計畫：
Management Studio 提供顯示實際執行計畫和顯示估計執行計畫等功能，這將用圖形方式來呈現計劃。這些功能為直接檢查提供了最適合的解決方案，是目前最常用的顯示和分析執行計畫的方法（在本文中，我將使用以這種方式產生的圖表計劃來說明我的樣本）。
各種 SET 選項，例如 SHOWPLAN_XML 和 SHOWPLAN_ALL，會以 XML 文檔或行集的形式返回執行計畫，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
執行計畫樣本 (單擊該映像獲得較小視圖)
圖 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
查詢的總估計執行開銷
由於我從 Sales.Customer 表中返回的只是 CustomerID 列，而此列又是 Sales.SalesOrderHeader 表中的外鍵，因此，我能夠使用以下代碼從查詢中完全消除產生的 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 表的執行計畫 (單擊該映像獲得較小視圖)
以下兩個操作被完全消除：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
最佳化後的執行計畫 (單擊該映像獲得較小視圖)
對 SalesOrderDetail表進行的叢集索引掃描已被非叢集索引掃描取代，後者的 I/O 開銷要小得多。我還消除了其中的一個“計算標量”運算子，因為我的索引中包含已計算出的 LineTotal 列的值。現在，估計執行計畫開銷是 2,28112，並會在執行查詢時進行 1,125 次邏輯讀取。
覆蓋索引
我在 SalesOrderDetail上建立的索引是所謂的“覆蓋索引”的一個樣本。它是一個包含填充查詢所需的所有列的非叢集索引，無需使用表掃描運算子或叢集索引掃描運算子掃描整個表。此索引實際上是表的一個小型副本，包含該表中所有列的一個子集。只有需要響應查詢的那些列才包含在該索引中，換句話說，該索引僅包含需要“覆蓋”查詢的列。
為最常執行的查詢建立覆蓋索引是查詢最佳化中使用的最簡單最常見的一種技術。當表中包含很多列，但查詢最常引用的只有很少幾列時，此類索引尤其適用。通過建立一個或多個覆蓋索引，可以大大改進受影響的查詢的效能，因為這些索引要訪問的資料量小得多，從而使產生的 I/O開銷更少。但在執行資料修改操作（插入、更新和刪除）期間，還存在維護附加索引所需的隱含開銷。您應根據您的環境以及 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 Edition 或 Developer Edition 中，查詢最佳化工具會自動將此查詢與索引檢視表定義匹配，並使用索引檢視表，而不是查詢所涉及的基表。圖 6顯示了本樣本中產生的執行計畫。此計劃僅由一個操作組成：對我在視圖上建立的索引進行叢集索引掃描。估計執行開銷僅為 0,09023，並且只執行 92 次邏輯讀取。
圖 6
使用索引檢視表時的執行計畫 (單擊該映像獲得較小視圖)
在 SQL Server 的其他版本中，仍然可以建立並使用此索引檢視表，但為達到同樣的效果，必須使用 NOEXPAND 提示將查詢更改為直接參考該視圖，如下所示：
SELECT CustomerID, OrdersTotalAmt
FROM vTotalCustomerOrders WITH (NOEXPAND)
您可以看到，如果使用得當，索引檢視表可以成為一項非常強大的功能。它們在最佳化對大量資料進行彙總的查詢時最為有用。如果用在 Enterprise Edition 中，這些視圖對許多查詢都有用，並且不要求更改代碼。
識別要最佳化的查詢
我怎樣識別值得最佳化的查詢？我要尋找執行得最為頻繁的查詢，單獨執行這些查詢的開銷可能並不高，但執行這些查詢的總計開銷可能要比很少執行的大型查詢的開銷高得多。我並不是說您不應該最佳化大型查詢，只是覺得應先關注最常執行的查詢。那麼，您如何識別這些查詢呢？
遺憾的是，最可靠的方法有點複雜，涉及到跟蹤對您的伺服器執行的所有查詢，然後根據其簽名進行分組（即，帶有實際參數值的查詢文本將由預留位置取代，以識別相同的查詢類型，即便查詢是使用不同的參數值執行的也一樣）。這是一個複雜的過程，因為查詢簽名很難產生。Itzik Ben-Gan在其《Microsoft SQL Server 2005: T-SQL Querying》一書中介紹了一種解決方案，此解決方案使用 CLR使用者定義的函數和Regex。
還有一種比較簡單的方法，但可靠性較差。您可以依賴在執行計畫緩衝中為所有查詢保留的統計資料，並使用動態管理檢視查詢這些資料。圖 7中包含一個樣本查詢，此樣本向您顯示緩衝中累計邏輯讀取次數最高的 20個查詢的文本和執行計畫。對於快速識別會產生最高邏輯讀取次數的查詢而言，此查詢非常便利，但也存在一些限制。即，此查詢只顯示在執行查詢時緩衝了其計劃的那些查詢。如果有未緩衝的內容，這些內容就會丟失。
識別這些拙劣的查詢後，您可以查看其查詢計劃，並利用我在本文中介紹的一些索引技術來尋找改進其效能的方法。如果能夠成功完成，表示您的時間沒有白費。
快樂最佳化！
Maciej Pilecki是 Solid Quality Mentors 的准顧問，這是一家專門致力於培訓、指導和諮詢的全球性組織。他是 Microsoft 認證培訓師 (MCT) 和 SQL Server 最有價值專家 (MVP)，並經常講授關於 SQL Server 和應用程式開發的諸多方面的課程並在討論此類內容的會議上講話。
本文轉自
http://idoall.org/blogs/ian/archive/2007/11/14/sql-server.aspx
