Are There Any Way To Programmatically Execute A Query With Include Actual Execution Plan And See Whether Any Index Suggestion Or Not

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 t

Solution 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.

privatestaticstringGetXmlPlanForQuery(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 asstring;
            }
        }
    }
    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"?><ShowPlanXMLxmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"Version="1.2"Build="11.0.5058.0"><BatchSequence><Batch><Statements><StmtSimpleStatementText="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"><StatementSetOptionsQUOTED_IDENTIFIER="true"ARITHABORT="false"CONCAT_NULL_YIELDS_NULL="true"ANSI_NULLS="true"ANSI_PADDING="true"ANSI_WARNINGS="true"NUMERIC_ROUNDABORT="false"/><QueryPlanDegreeOfParallelism="1"CachedPlanSize="16"CompileTime="1"CompileCPU="1"CompileMemory="192"><MissingIndexes><MissingIndexGroupImpact="94.0522"><MissingIndexDatabase="[exampleDb]"Schema="[dbo]"Table="[CLIENTS]"><ColumnGroupUsage="EQUALITY"><ColumnName="[ACTIVE]"ColumnId="15"/></ColumnGroup><ColumnGroupUsage="INCLUDE"><ColumnName="[TOTAL_SALES]"ColumnId="18"/></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfoSerialRequiredMemory="0"SerialDesiredMemory="0"/><OptimizerHardwareDependentPropertiesEstimatedAvailableMemoryGrant="830838"EstimatedPagesCached="207709"EstimatedAvailableDegreeOfParallelism="2"/><RelOpNodeId="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><ColumnReferenceDatabase="[exampleDb]"Schema="[dbo]"Table="[CLIENTS]"Column="TOTAL_SALES"/></OutputList><RunTimeInformation><RunTimeCountersPerThreadThread="0"ActualRows="315"ActualEndOfScans="1"ActualExecutions="1"/></RunTimeInformation><IndexScanOrdered="0"ForcedIndex="0"ForceScan="0"NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReferenceDatabase="[exampleDb]"Schema="[dbo]"Table="[CLIENTS]"Column="TOTAL_SALES"/></DefinedValue></DefinedValues><ObjectDatabase="[exampleDb]"Schema="[dbo]"Table="[CLIENTS]"Index="[imp_clpk_CLIENTS]"IndexKind="Clustered"/><Predicate><ScalarOperatorScalarString="[exampleDb].[dbo].[CLIENTS].[ACTIVE]=(0)"><CompareCompareOp="EQ"><ScalarOperator><Identifier><ColumnReferenceDatabase="[exampleDb]"Schema="[dbo]"Table="[CLIENTS]"Column="ACTIVE"/></Identifier></ScalarOperator><ScalarOperator><ConstConstValue="(0)"/></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp><ParameterList><ColumnReferenceColumn="@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.

staticvoidMain(string[] args)
{
    string result = GetXmlPlanForQuery("select TOTAL_SALES from clients where ACTIVE = 0;");
    XmlSerializer ser = newXmlSerializer(typeof(ShowPlanXML));
    var plan = (ShowPlanXML)ser.Deserialize(newStringReader(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.