Programatically read SQL Server's query plan suggested indexes for a specific execution of SQL?

Question

If I run this command in SSMS:

set showplan_xml on
GO
exec some_procedure 'arg1', 'arg2','arg3'
GO
set showplan_xml off
GO

I get XML output of the full call stack involved in the query execution, as well as any suggested indexes etc.

How might one read this from C#?

(One use case might be to periodically enable this and log these results in a production environment to keep an eye on index suggestions.)

Answer 1

This is, for the most part, two separate (though related) questions.

Is it possible to capture or somehow get the Missing Index information?

• If you want only the Suggested Indexes (and don't care about the rest of the execution plan), then you would probably be better off using the DMVs associated with missing indexes. You just need to write some queries instead of app code. Of course, DMV info is reset whenever the service restarts, but you can capture query results into a table if you want/need to keep a history. Please see the following MSDN pages for full details:

  • sys.dm_db_missing_index_groups
  • sys.dm_db_missing_index_group_stats
  • sys.dm_db_missing_index_details
  • sys.dm_db_missing_index_columns

  The only benefit that I can see to capturing the Execution Plan to get this info is that it would include the query text that resulted in the suggestion, which obviously is great for doing that research for determining which indexes to implement, but will also potentially explode the number of rows of data if many variations of a query or queries result in the same suggested index. Just something to keep in mind.

• Do not implement suggested indexes programmatically. They are for review and consideration. They are assessed per each query at that moment, and do not take into account:
  • how many indexes are already on the table
  • what other queries might benefit from a similar index (meaning, there could be a combination of fields that is not apparent to any individual query, but helps 3 or more queries, and hence only adds one index instead of 3 or more to the table).

Is it possible to programmatically capture execution plans?

Yes, this is definitely doable and I have done it myself. You can do it in .NET whether it is a Console App, Windows Form, Web App, SQLCLR, etc.

Here are the details of what you need to know if you want to capture XML plans:

• XML Execution plans are:
  • sent as separate result sets
  • sent as datatype of NVARCHAR / string
  • of two types: Estimated and Actual
• ESTIMATED plans:
  • are just that: estimated
  • are returned if you execute: SET SHOWPLAN_XML ON;
  • return only 1 plan that will contain multiple queries if there was more than 1 query in the batch
  • will return plans for simple queries such as SELECT 1 and DECLARE @Bob INT; SET @Bob = 52;
  • do not execute any of the queries. Hence, this method will return a single result set being the execution plan
• ACTUAL plans:
  • are the real deal, yo!
  • are returned if you execute: SET STATISTICS XML ON;
  • return 1 plan per query as a separate result set
  • will not return plans for simple queries such as SELECT 1 and DECLARE @Bob INT; SET @Bob = 52;
  • Execute all queries in the batch. Hence,
    • Per query, this method will return one or two result sets: if the query returns data, then the query results will be the first result set, and the execution plan will be either the only result set (if the query doesn't return data) or the second result set
    • For multiple queries, the execution plans will be interspersed with any query results. But, since some queries do not return any results, you cannot simple capture every other result set. I test for a single field in the result set, of type NVARCHAR, with a field name of Microsoft SQL Server 2005 XML Showplan (which has been consistent, at least up through SQL Server 2014; I haven't yet tested SQL Server 2016).
    • for testing purposes you might want to wrap these queries in a BEGIN TRAN; / COMMIT TRAN; so that no actual data modifications occur.

• SET commands need to be in their own batch, so get plans via something like:

  SqlConnection _Connection = new sqlConnection(_ConnectionStringFromSomewhere);
  SqlCommand _Command = _Connection.CreateCommand();
  SqlDataReader _Reader = null;
  
  try
  {
    _Connection.Open();
  
    // SET command needs to be in its own batch
    _Command.CommandText = "SET something ON";
    _Command.ExecuteNonQuery();
  
    // Now we can run the desired query
    _Command.CommandText = _QueryToTest;
    _Reader = _Command.ExecuteReader();
  
    ..get you some execution plans!
  }
  finally
  {
    if (_Reader != null)
    {
      _Reader.Dispose();
    }
    _Command.Dispose();
    _Connection.Dispose();
  }
  

---

As a final note I will mention that for anyone interested in capturing execution plans but not interested in writing any code to get them, I have already implemented this as a SQLCLR stored procedure. The procedure gets not only the XML Execution Plan(s), but also the output from STATISTICS TIME and STATISTICS IO, both of which are harder to capture as they are returned as messages (just like PRINT statements). And, the results of all 3 types of output can be captured into tables for further analysis across multiple executions (handy for doing A / B comparisons of current and revised code). This is available in the SQL# SQLCLR library (which again, I am the author of). Please note that while there is a Free version of SQL#, this particular stored procedure, DB_GetQueryInfo, is only available in the Full version, not the Free version.

---

UPDATE:
Interestingly enough, I just ran across the following MSDN article that describes how to use SQLCLR to grab the estimated plan, extract the estimated cost, pass it back as an OUTPUT parameter of the SQLCLR Stored Procedure, and then make a decision based on that. I don't think I would use it for such a purpose, but very interesting given that the article was written in 2005:

Processing XML Showplans Using SQLCLR in SQL Server 2005