導讀：
　　優化數據庫服務器時，您需要優化個人查詢性能。這與優化服務器安裝的其他影響硬件和軟件配置等性能的方面一樣重要 — 甚至更為重要。
　　即使數據庫服務器是在功能最強大的硬件上運行，它的性能還是可能會因為一些行為錯誤的查詢而受到負面影響。實際上，只要有一個壞的查詢（有時稱為“失控查詢”），就可能導致數據庫出現嚴重的性能問題。
　　反之，通過優化一組成本最高或最常執行的查詢，可以極大地提高數據庫的整體性能。在本文中，我將探討一些技術，您可使用這些技術識別并優化服務器上成本最高和性能最壞的查詢。

分析執行計劃
　　優化個人查詢時，通常通過查看查詢的執行計劃開始。此執行計劃描述 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用戶定義的函數和正則表達式。
　　還有一種比較簡單的方法，但可靠性較差。您可以依賴在執行計劃緩存中為所有查詢保留的統計數據，并使用動態管理視圖查詢這些數據。圖 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

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1920782