调整SQLSERVER非最优执行计划

我们发出的 SQL 语句，如果没有对应的缓存，优化器都会创建一个相应的 执行 计划 。但是，优化器基于成本的优化过程，在面对比较复杂的 SQL 语句时，不会考虑所有的情况。因此有些时候，就会给出一个近似高效的 执行 计划 。同时，受生产环境负载的影响，可

    我们发出的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本身也是个耗能大户，如果相比有太多的数据时就不太合适了。大家可以自己试一下。丢车保帅，不同的查询条件两者的开销也会发生变化。我们唯有做好最坏的打算，防止因为选择度的变化造成的这种性能开销。基本的原则就是避免对增长很快的大表扫描，分解复杂的查询以减少优化器优化时考虑各种组合的可能性。因为它并不清楚你查询的逻辑到底是怎么样的。最终的执行结果如下。