Geek Answers Handbook

Programmatically read suggested SQL Server query indexes for specific SQL execution?

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 the XML output of the full stack of calls involved in the query, as well as any suggested indexes, etc.

How can I read this with C #?

(One use case may be to periodically incorporate this and record these results in a production environment in order to keep track of index offers.)

+7
c # sql-server sql-execution-plan sqlclr
source share
1 answer

These are, for the most part, two separate (albeit related) issues.

Can I capture or retrieve information about a missing index?

  • If you want only the suggested indexes (and don't care about the rest of the execution plan), you probably would be better off using DMVs associated with missing indexes. You just need to write some queries instead of application code. Of course, the DMV information is reset whenever the service is rebooted, but you can capture the query results into a table if you want / need to save the history. For more information, see the following MSDN pages:

    The only advantage I can see to fix the Implementation Plan in order to get this information is that it will include a query text that would lead to a proposal, which is obviously great for conducting this study to determine which indexes will be implemented, but will also potentially explode the number of rows of data if many query or query variants result in the same proposed index. Just need to keep in mind.

  • Do not implement the proposed indexes programmatically. They are intended for review and consideration. They are evaluated for each request at this moment and do not take into account:
    • how many indexes are already indicated in the table
    • which other queries may be useful from a similar index (which means that there may be a combination of fields that is not obvious for any particular query, but helps 3 or more queries and, therefore, adds only one index instead of 3 or more tables).

Is it possible to programmatically display execution plans?

Yes, it is definitely doable, and I did it myself. You can do this in .NET, whether it be a console application, Windows Form, a web application, SQLCLR, etc.

The following is information about what you need to know if you want to capture XML plans:

  • XML Execution Plans:
    • sent as separate result sets
    • sent as NVARCHAR / string data type
    • two types: Estimated and Actual
  • EVALUATION :
    • - it's simple: priced
    • returned if you execute: SET SHOWPLAN_XML ON;
    • returns only 1 plan, which will contain several requests, if the package had more than 1 request
    • will return plans for simple queries such as SELECT 1 and DECLARE @Bob INT; SET @Bob = 52; DECLARE @Bob INT; SET @Bob = 52;
    • does not fulfill any of the requests. Therefore, this method returns a single result set, which is the execution plan.
  • ACTUAL PLANS :
    • - the real deal, yo!
    • are returned if you execute: SET STATISTICS XML ON;
    • returns 1 plan per request as a separate set of results
    • will not return plans for simple queries such as SELECT 1 and DECLARE @Bob INT; SET @Bob = 52; DECLARE @Bob INT; SET @Bob = 52;
    • Fulfill all requests in the package. Consequently,
      • In a query, this method returns one or two result sets: if the query returns data, 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) or the second result set
      • For multiple queries, execution plans will alternate with any query results. But, since some queries do not return any results, you cannot just capture every other set of results. I am testing a single field in an NVARCHAR type result set with a Microsoft SQL Server 2005 XML Showplan field name (which was matched at least with SQL Server 2014; SQL Server 2016 has not been tested yet).
      • for testing purposes, you might want to wrap these queries in a BEGIN TRAN; / COMMIT TRAN; so that no actual data changes occur.

  • SET commands should be in their own party, so get plans through 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 mentioned that for everyone who is interested in capturing execution plans, but not interested in writing code to get them, I already implemented this as a stored procedure SQLCLR. The procedure receives not only the XML execution plan, but also the output from STATISTICS TIME and STATISTICS IO , which are more difficult to capture because they are returned as messages (like PRINT statements). And the results of all three types of output can be captured in tables for further analysis in several versions (convenient for performing A / B comparisons of the current and revised code). This is available in the SQLCLR SQL # library (which again, I'm the author). Note that if you have a free version of SQL #, this particular DB_GetQueryInfo stored procedure is only available in the full version, not in the free version.

UPDATE:
Interestingly, I just ran into the next MSDN article that describes how to use SQLCLR to capture a settlement plan, extract the estimated value, pass it as the OUTPUT parameter of the SQLCLR stored procedure, and then make a decision based on this. I do not think that I will use it for such a purpose, but it is very interesting, given that the article was written in 2005:

Processing XML Sample Plans Using SQLCLR in SQL Server 2005

+9
source share

More articles:


All Articles