調整SQLSERVER非最優執行計畫

    我們發出的SQL語句，如果沒有對應的緩衝，最佳化器都會建立一個相應的執行計畫。但是，最佳化器基於成本的最佳化過程，在面對比較複雜的SQL語句時，不會考慮所有的情況。因此有些時候，就會給出一個近似高效的執行計畫。同時，受生產環境負載的影響，可能最佳化的過程會更不徹底，因此我們就應該控制語句的複雜程度，以減少最佳化器考慮各種組合的可能性。

當系統的效能出現問題時，即便你的索引建的很完美，但有的時候因為選擇度的問題，你還要考慮怎麼樣在選擇度不高的時候避免對錶的掃描。防止像在高速公路塞車一樣，所有的查詢都要等待再等待，就像公交車一樣。雖然SQL2005中有INCLUDE的功能，打破了在建立非叢集索引時16個欄位900個位元組的限制。但包含過多的INCLUE欄位的代價是浪費太多的磁碟空間。當然，我們可能不在乎磁碟空間開銷，畢竟是客戶買單。面對如此大的索引資料量，SQL2005也增加了備份的策略。用檔案或檔案組的方式來處理。但多檔案或檔案組的備份是基於多個備份基準的，因此給管理帶來了一定的挑戰性。所以，我們應該首先考慮好用既有的索引來最佳化查詢。實在沒有辦法時才去考慮建立索引或調整索引的欄位。沒有最好的，只有追求一個更合適的索引，盡量減少建立太多的索引。因為這會給資料的修改造成負擔。

在進行語句級的調優時，我們首先要明確一下調優的目的是什麼。在有了合適的索引時，就是如何有效利用它們在CPU、記憶體、I/O之間達到一個平衡。如果你的記憶體一直很緊張，我們就想辦法避免那些佔用太多記憶體的運算子的使用。每個運算子在特定的場合使用是很高效的，沒有什麼是一成不變的。只有我們多試，才能找到一個最佳的平衡點。

下面我們通過一個樣本來示範一下對一個SQLSERVER產生的不是很高效的執行計畫的調整方法。調整前後的成本開銷為9:1，這樣就能提高系統的並行作業。

 

Code
SELECT P.Name,P.Color,PSC.Name AS SubcategoryName,PC.Name AS CategoryName,
    S.SalesOrderID,S.OrderDate,OD.LineTotal
    FROM Production.Product P
    JOIN Production.ProductSubcategory PSC
        ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
    JOIN Production.ProductCategory PC
        ON PSC.ProductCategoryID = PC.ProductCategoryID
    JOIN Sales.SalesOrderDetail OD
        ON OD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader S
        ON OD.SalesOrderID = S.SalesOrderID
WHERE S.SalesPersonID = 275 AND PSC.Name = N'Road Bikes'

 這個查詢要查出某個銷售人員的某個子類產品的銷售情況及相關的產品的資訊。我們知道該銷售人員的所有銷售產品中只有一部分會屬於某一類的產品。因此，最終要查詢的結果是下面兩者的交集部分。隨著兩者交集部分的變化，SQLSERVER給出的總體的查詢思路沒有多大變化，因此我們應該進行幹涉了。

 

下面是SQLSERVER為我們產生的執行計畫：

部分圖形計劃顯示如下：

大家看到計劃中對Sales.SalesOrderHeader表進行了一次掃描，而這張表是一個增長很快的表，所以對這樣的表進行掃描是一種很耗時的查詢。掃描是因為查詢中有OrderDate，而這個欄位沒有索引。所以只有在叢集索引的葉級，也就是真正的資料頁上才能獲得此資訊。同時，Sales.SalesOrderDetail中對應了很多訂單明細項，這也是增長很快的表。這裡的叢集索引尋找是因為要查詢LineTotal，這是個計算欄位，上面同樣也沒建立索引。為了計算這個值，會消耗很多的CPU資源。

    我們知道查詢中聯結產品和訂單的表是Sales.SalesOrderDetail，如果我們能通過唯有的兩個查詢條件先在索引層級中把兩者的交集取出來，最終再去查詢只在資料頁級存在的資料就會減少很多的資源浪費。下面是調整後的查詢過程：

1. 用於儲存銷售人員銷售的產品和該類產品的交集部分的表變數，此處使用表變數可以防止在過程中引起重新編譯。http://www.cnblogs.com/tom-fu/archive/2008/03/09/1096993.html
   Code
   DECLARE @udt_sales TABLE
   (
       SalesOrderID          INT NOT NULL,
       SalesOrderDetailID    INT NOT NULL
   )

2. 用於儲存某類產品相關資訊的表變數
   Code
   DECLARE @udt_products TABLE
   (
       ProductID            INT    NOT NULL,
       [Name]              [Name] NOT NULL,
       Color               nvarchar(15) NULL,
       SubcategoryName     [Name] NOT NULL, 
       CategoryName        [Name] NOT NULL
   )    

3. 因為Sales.SalesOrderHeader在SalesPersonID欄位有非叢集索引，所以查詢275的訂單可直接在此索引中尋找。同時，我們看到在Sales.SalesOrderDetail表的ProductID欄位建了一個非叢集索引，而SalesOrderID,SalesOrderDetailID作為叢集索引是該索引的索引值欄位。所以只在這個非叢集索引中即可查詢到SalesOrderID和SalesOrderDetailID，從而減少I/O的操作。執行過程如所示 Code
   INSERT INTO @udt_sales
   SELECT OD.SalesOrderID,OD.SalesOrderDetailID 
   FROM Sales.SalesOrderHeader S
       JOIN Sales.SalesOrderDetail OD 
           ON OD.SalesOrderID = S.SalesOrderID AND S.SalesPersonID = 275 
       JOIN (Production.ProductSubcategory PSC
               JOIN Production.Product P    
                   ON P.ProductSubcategoryID = PSC.ProductSubcategoryID AND PSC.Name = N'Road Bikes')
           ON P.ProductID=OD.ProductID

1. 把產品相關的資訊存於表變數中以避免在聯結中多次查詢這些表
   Code
   INSERT INTO @udt_products
   SELECT P.ProductID,P.Name,P.Color,PSC.Name,PC.Name
   FROM Production.Product P    
           JOIN Production.ProductSubcategory PSC
               ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
           JOIN Production.ProductCategory PC
               ON PSC.ProductCategoryID = PC.ProductCategoryID
   WHERE PSC.Name = N'Road Bikes'

2. 最終用取得的交集部分和訂單及明細表聯結，查詢出S.OrderDate,OD.LineTotal。因為這時是用取得的較小的交集部分來查詢，所以避免了對Sales.SalesOrderHeader的表掃描。 Code
   SELECT UP.Name,UP.Color,UP.SubcategoryName,UP.CategoryName,
           S.SalesOrderID,S.OrderDate,OD.LineTotal
   FROM @udt_sales US
           INNER JOIN Sales.SalesOrderHeader S
               ON US.SalesOrderID=S.SalesOrderID
           JOIN Sales.SalesOrderDetail OD 
               ON US.SalesOrderID=OD.SalesOrderID AND US.SalesOrderDetailID=OD.SalesOrderDetailID
           JOIN @udt_products UP
               ON OD.ProductID=UP.ProductID

     成本的開銷大，不一定代表執行時間就慢。如果你在機器上執行，因為受語句執行時機器的資源使用方式，所以不能只單純依靠執行時間來判斷，如果你追求更快的速度可以想辦法把它改成並行的方式，這時就會降低系統的並發性。當然如果在不影響並發性的情況下，SQLSERVER也會主動的選擇使用並發的方式。把最佳化前後的語句分別一前一後的去執行，你會得到不同的執行時間。所以最終還是要看I/O和裡面所包含的各個運算子的操作。同時，如果你的查詢能佔用更少的資源，則能提高系統的並發性。這樣在總體上來講，你的系統效能還是會提高一些。

    當然，如果再結合一些提示的使用可能還有更高效的查詢方法，或是再調整一下執行的邏輯。同時，我所舉的樣本只是在查詢條件的選擇度不高時的情況，如果查詢條件選擇度很高的話，SQLSERVER執行的整個過程也不會和現在的這個樣。INSERT INTO本身也是個耗能大戶，如果相比有太多的資料時就不太合適了。大家可以自己試一下。丟車保帥，不同的查詢條件兩者的開銷也會發生變化。我們唯有做好最壞的打算，防止因為選擇度的變化造成的這種效能開銷。基本的原則就是避免對增長很快的大表掃描，分解複雜的查詢以減少最佳化器最佳化時考慮各種組合的可能性。因為它並不清楚你查詢的邏輯到底是怎麼樣的。最終的執行結果如下。