c# - Are there any way to programmatically execute a query with Include Actual Execution Plan and see whether any index suggestion or not

Question

I have a quite good number of queries and i want to test each of them with Include Actual Execution Plan feature on sql server management studio

However it is not possible for me to do this manually for 1m + queries

So i wonder can i execute them programmatically (from c#) with Include Actual Execution Plan feature and see whether SQL server suggests any index or not

Answer 1

First, before I go in to how to get the Actual Execution Plan in code and find the ones that report needing indexes I would recommend you look in to using the Database Engine Tuning Adviser (DTA), you can feed it a list of all the queries and it will process them telling you possible indexes, statistics, and many other things that can help out planning your queries.

Even better than giving it a list of 1m+ queries is you can get a trace from the server with the actual queries that are being run and it will focus on the queries that are taking up the most time.

---

To answer your original question you will need to add SET STATISTICS XML ON at the start of the connection, this will give you the XML data that the GUI you showed is based off of. (See here for more info about getting the plans). Once you do that your queries will return with a extra result set containing the xml for the plan in the first row of the first column.

Here is a quick and dirty function that does that.

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;
}

And here is the XML it returned for the query select TOTAL_SALES from clients where ACTIVE = 0; that I ran that I had on one of my local databases.

<?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>

Now, because Microsoft is quite nice, if you navigate to the namespace listed in the XML you can actually get a copy of the .xsd for the format. You can then from the developer's command prompt do xsd showplanxml.xsd /classes and it will give you a showplanxml.cs that you can use with the XmlSerializer.

Here is a small example program that does a debugger break on a missing index.

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();
}

I used XmlSerializer and deseralized it to a class but you could just as easily loaded this in to a XDocument then used XPath to find all the nodes named MissingIndex.