通过编程读SQL Server的查询计划建议的SQL的具体执行索引？ [英] Programatically read SQL Server's query plan suggested indexes for a specific execution of SQL?

问题描述

如果我运行在SSMS这个命令：

 上
 GO 
 EXEC some_procedure设置SHOWPLAN_XML ARG1，ARG2，ARG3'
 GO 
组SHOWPLAN_XML关闭
 GO 
  

我得到的查询中涉及的执行充分调用堆栈的XML输出，以及任何建议的指标等。

一个人怎么可能会从阅读本C＃？

（一个用例可能是定期启用此并记录这些成果在生产环境中盯紧指标的建议。）

解决方案

这是，在大多数情况下，两个单独的（尽管有关的）问题。

是否有可能捕获或以某种方式获取缺失索引信息？

• 如果你想的只有的了建议索引（和不关心执行计划的其余部分），那么你很可能会关闭使用带有缺失索引相关的动态管理视图更好。你只需要编写一些查询，而不是应用程序代码。当然，DMV信息时重置服务重新启动，但你可以捕获查询结果到表中，如果你想/需要保持的历史。请参阅下面的MSDN网页的全部细节：

  
  
  
  
  
  • 的 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
  
  
  
  
  

  这是我可以看到捕捉唯一的好处执行计划得到这个信息是，这将包括造成的建议，这显然是伟大的，这样做的研究来确定哪些指标来实现查询文本，但也可能会爆炸的许多变化的数据行数查询或查询产生相同的建议索引。只是要记住。



• 请的不的工具编程建议索引。他们是审查和审议。他们在那一刻，每每个查询进行评估，并没有考虑到：
  
  
  
  
  • 有多少指标都已经在桌子上
  
  
  • 什么其他查询可能从类似的索引（含义中受益，有可能是因为没有明显的任何个别查询字段的组合，但有助于3个或更多的查询，并因此只增加了一个索引，而不是3个或更多表）。
  
  

是否有可能以编程方式捕获执行计划<？ / H2>

是的，这绝对是可行的，我做了我自己。你可以做到这一点在.NET是否是一个控制台应用程序，Windows窗体，Web应用程序，SQLCLR等。

下面是你需要知道，如果有什么细节要捕获的XML计划：

• XML执行计划：
  
  
  
  
  • 被作为单独的结果集
  
  
  • 发送为 NVARCHAR / 字符串的数据类型
  
  <两类LI> 预估和实际
  


• 估算计划：
  
  
  
  
  • 是正义的：估计
  
  
  • 如果执行返回： SET SHOWPLAN_XML ON;
  
  
  • 返回只有1个计划，如果有超过1查询，将包含多个查询在批
  
  
  • 将返回简单查询计划，如 SELECT 1 和定义@Bob INT; SET @Bob = 52;
  
  
  • 请的不的执行任何查询。因此，此方法将返回一个结果集作为执行计划
  
  


• 实际计划：
  
  
  
  
  • 是实打实的，哟
  
  
  • 如果您执行返回：<！code> SET STATISTICS XML ON;
  
  
  • 返回1计划的每次查询的作为一个单独的结果集
  
  
  • 将不的恢复计划简单查询，如 SELECT 1 和定义@Bob INT; SET @Bob = 52;
  
  
  • 执行所有查询的批次。因此，
    
    
    
    
    • 的每查询的，此方法将返回1的或的两个结果集：如果查询返回数据，则查询结果将是第一个结果集，并执行计划将是本唯一的结果集（如果查询不返回数据）或第二个结果集
    
    
    • 对于多个查询，执行计划将与任何查询结果穿插。但是，因为某些查询不返回任何结果，你不能简单的捕捉每一个其他结果集。我在结果集中测试单个字段中，键入 NVARCHAR ，用微软SQL Server 2005的XML显示计划（这一直是一个字段名一致的，通过SQL Server 2014年至少可达;我还没有测试过的SQL Server 2016）
    
    
    • 用于测试目的你可能想在包装这些查询一个 BEGIN TRAN; / COMMIT TRAN; ，以便不发生实际数据修改
    
    
  
  



• SET 命令需要在自己的批次，因此获得通过的计划是这样的：

  
  
  <预类=郎-CS prettyprint-覆盖> SqlConnection的_Connection =新的SqlConnection（_ConnectionStringFromSomewhere）;
的SqlCommand _Command = _Connection.CreateCommand（）;
SqlDataReader的_Reader = NULL;

试
{
_Connection.Open（）;

// SET命令需要在自己的一批
_Command.CommandText =设置上的东西;
_Command.ExecuteNonQuery（）;

//现在我们可以运行所需的查询
_Command.CommandText = _QueryToTest;
_Reader = _Command.ExecuteReader（）;

..get你一些执行计划！
}
终于
{
如果（_Reader！= NULL）
{
_Reader.Dispose（）;
}
_Command.Dispose（）;
_Connection.Dispose（）;
}


---

最后一点，我会提到，有兴趣的人捕获的执行计划，但的不的兴趣写任何代码，让他们，我已经实现了这个作为一个SQLCLR存储过程。该过程得到不仅是XML执行计划（S），也可以从输出STATISTICS TIME 和统计IO ，这两者都难以捕捉到，因为它们是作为消息返回（就像打印语句）。而且，所有3个类型的输出结果可以被捕获到横跨多个执行进一​​步的分析表（方便做的电流和修改后的代码A / B比较）。这可在 SQL＃ SQLCLR库（这再次，我的作者）。请注意，虽然是SQL＃免费版本，这个特殊的存储过程， DB_GetQueryInfo ，只有在完整版，而不是免费的版本。

---

更新：结果
有趣的是，我只是跨介绍如何使用SQLCLR以下MSDN文章跑抢估计计划，提取费用估计，它传回的存储过程，SQLCLR的输出参数，然后进行基于该决定。我不认为我会用它来做这样的目的，但很有趣因为该文章写于2005年：

的处理XML显示计划在SQL Server中使用SQLCLR 2005

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

解决方案

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

这篇关于通过编程读SQL Server的查询计划建议的SQL的具体执行索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！