有什么方法可以通过“包含实际执行计划”以编程方式执行查询，并查看是否有任何索引建议

Question

我有相当数量的查询，我想使用sql server management studio上的“包括实际执行计划”功能对每个查询进行测试

但是，对于100万次以上的查询，我无法手动执行此操作

因此，我想知道我是否可以使用“包括实际执行计划”功能以编程方式（从C＃）执行它们，并查看SQL Server是否建议任何索引

Answer 1

首先，在继续学习如何在代码中获取实际执行计划并找到报告需要索引的代码之前，我建议您使用数据库引擎优化顾问（DTA）进行查找，您可以将所有查询，它将对其进行处理，并告诉您可能的索引，统计信息以及许多其他有助于计划查询的内容。

甚至比给它提供一百万个查询列表更好的是，您可以从服务器获取正在运行的实际查询的跟踪，并且它将集中于占用最多时间的查询。

要回答您的原始问题，您将需要SET STATISTICS XML ON在连接开始时添加，这将为您提供所显示的GUI所基于的XML数据。。完成后，您的查询将返回一个额外的结果集，该结果集在第一列的第一行中包含计划的xml。

这是一个快速而肮脏的功能。

private static string GetXmlPlanForQuery(string queryText)
{
    string result = null;
    using (var connection = new SqlConnection(connectionString))
    using (var command = new SqlCommand())
    {
        connection.Open();
        command.Connection = connection;

        //Enable the statistics.
        command.CommandText = "SET STATISTICS XML ON";
        command.ExecuteNonQuery();

        //Run through the query, keeping the first row first column of the last result set.
        command.CommandText = queryText;
        using (var reader = command.ExecuteReader())
        {
            object lastValue = null;
            do
            {
                if (reader.Read())
                {
                    lastValue = reader.GetValue(0);
                }
            } while (reader.NextResult());

            if (lastValue != null)
            {
                result = lastValue as string;
            }
        }
    }
    return result;
}

这是它针对select TOTAL_SALES from clients where ACTIVE = 0;我在本地数据库之一上运行的查询返回的XML
。

<?xml version="1.0"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.5058.0">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT [TOTAL_SALES] FROM [clients] WHERE [ACTIVE]=@1" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.0767454" StatementEstRows="315" StatementOptmLevel="FULL" QueryHash="0x708AE72DD31A316" QueryPlanHash="0x214EA79FF76E6771" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/>
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="192">
            <MissingIndexes>
              <MissingIndexGroup Impact="94.0522">
                <MissingIndex Database="[exampleDb]" Schema="[dbo]" Table="[CLIENTS]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[ACTIVE]" ColumnId="15"/>
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[TOTAL_SALES]" ColumnId="18"/>
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/>
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="830838" EstimatedPagesCached="207709" EstimatedAvailableDegreeOfParallelism="2"/>
            <RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="315" EstimateIO="0.0749769" EstimateCPU="0.0017685" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0767454" TableCardinality="1465" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
              <OutputList>
                <ColumnReference Database="[exampleDb]" Schema="[dbo]" Table="[CLIENTS]" Column="TOTAL_SALES"/>
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="315" ActualEndOfScans="1" ActualExecutions="1"/>
              </RunTimeInformation>
              <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[exampleDb]" Schema="[dbo]" Table="[CLIENTS]" Column="TOTAL_SALES"/>
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[exampleDb]" Schema="[dbo]" Table="[CLIENTS]" Index="[imp_clpk_CLIENTS]" IndexKind="Clustered"/>
                <Predicate>
                  <ScalarOperator ScalarString="[exampleDb].[dbo].[CLIENTS].[ACTIVE]=(0)">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[exampleDb]" Schema="[dbo]" Table="[CLIENTS]" Column="ACTIVE"/>
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Const ConstValue="(0)"/>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Predicate>
              </IndexScan>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterCompiledValue="(0)" ParameterRuntimeValue="(0)"/>
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

现在，由于Microsoft非常出色，因此，如果您导航到XML中列出的名称空间，则实际上可以获取.xsd该格式的副本。然后，您可以从开发人员的命令提示符下执行do
xsd showplanxml.xsd/classes，它将为您提供一个showplanxml.cs可以与一起使用的XmlSerializer。

这是一个小的示例程序，该程序在缺少索引的情况下使调试器中断。

static void Main(string[] args)
{
    string result = GetXmlPlanForQuery("select TOTAL_SALES from clients where ACTIVE = 0;");
    XmlSerializer ser = new XmlSerializer(typeof(ShowPlanXML));
    var plan = (ShowPlanXML)ser.Deserialize(new StringReader(result));

    var missingIndexes =
        plan.BatchSequence.SelectMany(x => x)
            .SelectMany(x => x.Items)
            .OfType<StmtSimpleType>()
            .Select(x => x.QueryPlan)
            .Where(x => x.MissingIndexes != null && x.MissingIndexes.Any());

    foreach (var queryPlan in missingIndexes)
    {
        //This will hit for each statement in the query that was missing a index, check queryPlan.MissingIndexes to see the indexes that are missing.
        Debugger.Break();
    }

    Console.WriteLine("Done");
    Console.ReadLine();
}

我使用了XmlSerializer并将其反序列化为一个类，但是您可以轻松地将其加载到XDocument中，然后使用XPath查找所有名为的节点MissingIndex。