使用SQL2005强制计划解决遗留系统性能问题

       面对遗留系统或第三方系统在程序中编写的SQL语句出现性能问题时，因没有相应的程序代码，在SQL2000下你也只能束手无措。SQL2005提供了强制计划的功能，可以部分的优化这些遗留语句的性能问题。为什么说部分呢？因为强制计划的选项只能使用OPTION里的提示。因此它只能优化部分ad-hoc查询和部分参数化的语句，这也是为什么要使用存储过程的重要性了。下面以一个示例来演示一下强制计划的三个制作步骤：

1. 捕获程序中发出的ad-hoc或参数化查询语句
2. 使用SET STATISTICS XML得到优化后的执行计划
3. 使用USE PLAN查询提示使用新的执行计划

强制计划的制作其实很简单，关键在于第2步怎么样生产一个最优的计划。

为了演示方便我们先在tempdb下执行以下脚本：

c=999并且b>102或d=10并且b>102的记录，这个查询很简单。 程序中代码如下所示：

USE tempdb;
GO
IF OBJECT_ID('t') IS NOT NULL
    DROP TABLE t
GO
CREATE TABLE t(a int, b int, c int, d int, e int, f int, g char(1000))
CREATE CLUSTERED INDEX cidx ON t(a, b, c, d, e, f)
CREATE INDEX idx2 ON t(c)
CREATE INDEX idx3 ON t(d)
GO
INSERT t VALUES(1, 500, 1, 1, 1, 1, 1)
INSERT t VALUES(10, 500, 10, 10, 10, 10, 10)
INSERT t VALUES(999, 500, 999, 999, 999, 999, 999)
GO

如果我们想查询

cnn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
cnn.Open();
SqlCommand cmd = cnn.CreateCommand();
SqlParameter paramDialogHandle1 = new SqlParameter("@b", SqlDbType.Int);
SqlParameter paramDialogHandle2 = new SqlParameter("@c", SqlDbType.Int);
SqlParameter paramDialogHandle3 = new SqlParameter("@d", SqlDbType.Int);
paramDialogHandle1.Value = 102;
paramDialogHandle2.Value = 99;
paramDialogHandle3.Value = 10;
cmd.Parameters.Add(paramDialogHandle1);
cmd.Parameters.Add(paramDialogHandle2);
cmd.Parameters.Add(paramDialogHandle3);
cmd.CommandText = "SELECT * FROM t WHERE b > @b AND (c = @c OR d = @d)";
cmd.ExecuteNonQuery();
cnn.Close();

 

1．使用profile查看该语句的执行计划如下，对表t做了一次表扫描。因为这个表现在只占用了一个数据页，因此它选择了表扫描是正确的。在此不讨论计划的正确性，只演示如何改变执行计划。

2．那现在该使用STATISTICS XML制作我们自己的执行计划了，如下语句所示：

--更改查询使用覆盖索引查询，把相应的XML计划作为强制计划的输入
SET STATISTICS XML ON;
GO
SELECT m.*,t.g
FROM (
SELECT a,b,c,d,e,f FROM t WHERE c = 999 AND b>102
UNION 
SELECT a,b,c,d,e,f FROM t WHERE d = 10 AND b>102
) AS m
 INNER JOIN t ON m.a=t.a AND m.b=t.b AND m.c=t.c AND m.d=t.d AND m.e=t.e AND m.f=t.f
SET STATISTICS XML OFF;
GO

 

点击结果集中的XML把所得到的执行计划保存下来。这也是最重要的一步，改变SQL语句得到相同的结果，充分利用现有索引。

3．制作强制计划

如果程序中是直接拼接的SQL语句，中间要多一步强制参数化的操作。要不然只能改变生成计划时匹配参数的语句。因此必须要进行强制参数化。但是强制参数化有时是适得其反的，进行强制参数化时一定要小心。

DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template N'SELECT * FROM t WHERE b > 102 AND (c = 999 OR d = 10)',
@stmt OUTPUT, 
@params OUTPUT

EXEC sp_create_plan_guide N'TemplateGuide1', 
@stmt = @stmt, 
@type = N'TEMPLATE', 
@module_or_batch = NULL, 
@params = @params, @hints = N'OPTION(PARAMETERIZATION FORCED)'

 

因为程序中已经参数化，所以可以直接使用在profiler中捕获到的语句：

exec sp_executesql N'SELECT * FROM t WHERE b > @b AND (c = @c OR d = @d)',N'@b int,@c int,@d int',@b=102,@c=99,@d=10  

把语句和参数部分分别赋给对应的参数，把第2步中得到的计划放在USE PLAN提示中。需要注意StatementText属性中如果有'时，一定要手工把它替换为''''。还有一定要使用从profiler中捕获的语句，尤其是在使用ad-hoc查询时，因为语句任何不一致都会导致生成另外的执行计划，所以在测试这类语句时一定要注意格式、大小写、空格等要一致。下面的语句可以创建一个强制计划：执行完强制计划后，再来运行一下程序从profiler中得到如下的结果，说明我们的计划生效了。还有一点要注意的是：如果SqlParameter在定义参数时没有指定参数类型，随着参数的变化，跟踪到的参数化查询中会自动生成不同的参数类型，这时你必须要为所有的参数类型组合才能使得计划有效。

 EXEC sp_create_plan_guide @name = N'Guide2',
    @stmt = N'SELECT * FROM t WHERE b > @b AND (c = @c OR d = @d)',
    @type = N'SQL',
    @module_or_batch = NULL ,
    @params = N'@b int,@c int,@d int',
    @hints = N'OPTION (USE PLAN
N''
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.4035.00">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT m.*,t.g FROM ( SELECT a,b,c,d,e,f FROM t WHERE c = 999 AND b&gt;100 UNION  SELECT a,b,c,d,e,f FROM t WHERE d = 10 AND b&gt;100 ) AS m  INNER JOIN t ON m.a=t.a AND m.b=t.b AND m.c=t.c AND m.d=t.d AND m.e=t.e AND m.f=t.f " StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0156172" StatementEstRows="1.97096" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
          <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
          <QueryPlan DegreeOfParallelism="0" CachedPlanSize="29" CompileTime="11" CompileCPU="11" CompileMemory="360">
            <RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.97096" EstimateIO="0" EstimateCPU="8.2386e-006" AvgRowSize="1031" EstimatedTotalSubtreeCost="0.0156172" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Column="Union1008" />
                <ColumnReference Column="Union1009" />
                <ColumnReference Column="Union1010" />
                <ColumnReference Column="Union1011" />
                <ColumnReference Column="Union1012" />
                <ColumnReference Column="Union1013" />
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <NestedLoops Optimized="0">
                <OuterReferences>
                  <ColumnReference Column="Union1008" />
                  <ColumnReference Column="Union1009" />
                  <ColumnReference Column="Union1010" />
                  <ColumnReference Column="Union1011" />
                  <ColumnReference Column="Union1012" />
                  <ColumnReference Column="Union1013" />
                </OuterReferences>
                <RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1.97096" EstimateIO="0" EstimateCPU="1.98548e-006" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0121714" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Column="Union1008" />
                    <ColumnReference Column="Union1009" />
                    <ColumnReference Column="Union1010" />
                    <ColumnReference Column="Union1011" />
                    <ColumnReference Column="Union1012" />
                    <ColumnReference Column="Union1013" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <StreamAggregate>
                    <DefinedValues />
                    <GroupBy>
                      <ColumnReference Column="Union1008" />
                      <ColumnReference Column="Union1009" />
                      <ColumnReference Column="Union1010" />
                      <ColumnReference Column="Union1011" />
                      <ColumnReference Column="Union1012" />
                      <ColumnReference Column="Union1013" />
                    </GroupBy>
                    <RelOp NodeId="2" PhysicalOp="Merge Join" LogicalOp="Concatenation" EstimateRows="2" EstimateIO="0" EstimateCPU="0.0056023" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0121695" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Column="Union1008" />
                        <ColumnReference Column="Union1009" />
                        <ColumnReference Column="Union1010" />
                        <ColumnReference Column="Union1011" />
                        <ColumnReference Column="Union1012" />
                        <ColumnReference Column="Union1013" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <Merge>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Union1008" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1009" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1010" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1011" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1012" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1013" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0032831" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Column="Uniq1002" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Uniq1002" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[tempdb]" Schema="[dbo]" Table="[t]" Index="[idx2]" TableReferenceId="1" />
                            <SeekPredicates>
                              <SeekPredicate>
                                <Prefix ScanType="EQ">
                                  <RangeColumns>
                                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                                  </RangeColumns>
                                  <RangeExpressions>
                                    <ScalarOperator ScalarString="(999)">
                                      <Const ConstValue="(999)" />
                                    </ScalarOperator>
                                  </RangeExpressions>
                                </Prefix>
                              </SeekPredicate>
                            </SeekPredicates>
                            <Predicate>
                              <ScalarOperator ScalarString="[tempdb].[dbo].[t].[b]&gt;(100)">
                                <Compare CompareOp="GT">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="(100)" />
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                        <RelOp NodeId="4" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0032831" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Column="Uniq1006" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Uniq1006" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[tempdb]" Schema="[dbo]" Table="[t]" Index="[idx3]" TableReferenceId="2" />
                            <SeekPredicates>
                              <SeekPredicate>
                                <Prefix ScanType="EQ">
                                  <RangeColumns>
                                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                                  </RangeColumns>
                                  <RangeExpressions>
                                    <ScalarOperator ScalarString="(10)">
                                      <Const ConstValue="(10)" />
                                    </ScalarOperator>
                                  </RangeExpressions>
                                </Prefix>
                              </SeekPredicate>
                            </SeekPredicates>
                            <Predicate>
                              <ScalarOperator ScalarString="[tempdb].[dbo].[t].[b]&gt;(100)">
                                <Compare CompareOp="GT">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="(100)" />
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                      </Merge>
                    </RelOp>
                  </StreamAggregate>
                </RelOp>
                <RelOp NodeId="6" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="1011" EstimatedTotalSubtreeCost="0.00343661" Parallel="0" EstimateRebinds="0.970957" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="2" ActualExecutions="2" />
                  </RunTimeInformation>
                  <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[tempdb]" Schema="[dbo]" Table="[t]" Index="[cidx]" TableReferenceId="3" />
                    <SeekPredicates>
                      <SeekPredicate>
                        <Prefix ScanType="EQ">
                          <RangeColumns>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                          </RangeColumns>
                          <RangeExpressions>
                            <ScalarOperator ScalarString="[Union1008]">
                              <Identifier>
                                <ColumnReference Column="Union1008" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[Union1009]">
                              <Identifier>
                                <ColumnReference Column="Union1009" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[Union1010]">
                              <Identifier>
                                <ColumnReference Column="Union1010" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[Union1011]">
                              <Identifier>
                                <ColumnReference Column="Union1011" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[Union1012]">
                              <Identifier>
                                <ColumnReference Column="Union1012" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[Union1013]">
                              <Identifier>
                                <ColumnReference Column="Union1013" />
                              </Identifier>
                            </ScalarOperator>
                          </RangeExpressions>
                        </Prefix>
                      </SeekPredicate>
                    </SeekPredicates>
                    <Predicate>
                      <ScalarOperator ScalarString="[tempdb].[dbo].[t].[b]&gt;(100)">
                        <Compare CompareOp="GT">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Const ConstValue="(100)" />
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Predicate>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>'')'