c# - 以编程方式读取 SQL Server 的查询计划建议的特定 SQL 执行的索引？

Question

如果我在 SSMS 中运行此命令:

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

我得到了查询执行中涉及的完整调用堆栈的 XML 输出，以及任何建议的索引等。

如何从 C# 中读取此内容？

(一个用例可能是定期启用此功能并在生产环境中记录这些结果以密切关注索引建议。)

Answer 1

在大多数情况下，这是两个独立(虽然相关)的问题。

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

如果您只需要建议的索引(并且不关心执行计划的其余部分)，那么您最好使用与缺失索引相关联的 DMV。您只需要编写一些查询而不是应用程序代码。当然，每当服务重新启动时，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 个或更多)。

是否可以以编程方式捕获执行计划？

是的，这绝对是可行的，而且我自己已经做到了。无论是控制台应用程序、Windows 窗体、Web 应用程序、SQLCLR 等，您都可以在 .NET 中执行此操作。

如果要捕获 XML 计划，以下是您需要了解的详细信息:

XML 执行计划是:

作为单独的结果集发送

作为 NVARCHAR/string

的数据类型发送

两种类型: 估计 和 实际

估计 计划:

就是这样:估计

如果执行，则返回

: SET SHOWPLAN_XML ON;

如果批处理中的查询超过 1 个，则仅返回 1 个将包含多个查询的计划

将返回简单查询的计划，例如 SELECT 1 和 DECLARE @Bob INT; SET @Bob = 52;

不执行任何查询。因此，此方法将返回单个结果集，即执行计划

实际 计划:

是真正的交易，哟!

如果执行，则返回

: SET STATISTICS XML ON;

每个查询返回 1 个计划作为单独的结果集

不会返回简单查询的计划，例如 SELECT 1 和 DECLARE @Bob INT; SET @Bob = 52;

批量执行所有查询。因此，

每个查询，此方法将返回一个或两个结果集:如果查询返回数据，则查询结果将是第一个结果集，执行计划将是唯一的结果集(如果查询没有返回数据)或第二个结果集

对于多个查询，执行计划中会穿插任何查询结果。但是，由于某些查询不返回任何结果，因此您不能简单地捕获所有其他结果集。我测试了结果集中的单个字段，类型为 NVARCHAR ，字段名称为 Microsoft SQL Server 2005 XML Showplan (这一直是一致的，至少到 SQL Server 2014 为止；我还没有测试 SQL Server 2016)。

出于测试目的，您可能希望将这些查询包装在 BEGIN TRAN;/COMMIT TRAN; 中，这样就不会发生实际的数据修改。

SET 命令需要在它们自己的批处理中，因此通过以下方式获取计划:

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

最后，我将提到，对于那些对捕获执行计划感兴趣但对编写任何代码来获取它们不感兴趣的人，我已经将其实现为 SQLCLR 存储过程。该过程不仅获得了 XML 执行计划，还获得了 STATISTICS TIME 和 STATISTICS IO 的输出，这两个都更难捕获，因为它们作为消息返回(就像 PRINT 语句一样)。而且，所有 3 种类型的输出的结果都可以捕获到表格中，以便在多次执行中进行进一步分析(方便对当前和修订后的代码进行 A/B 比较)。这在 SQL# SQLCLR 库中可用(我也是它的作者)。请注意，虽然有免费版的 SQL#，但这个特定的存储过程 DB_GetQueryInfo 仅在完整版中可用，在免费版中不可用。

更新:
有趣的是，我刚刚看到了以下 MSDN 文章，该文章描述了如何使用 SQLCLR 获取估计计划，提取估计成本，将其作为 SQLCLR 存储过程的 OUTPUT 参数传回，然后基于此做出决定。我不认为我会出于这样的目的使用它，但考虑到这篇文章是 2005 年写的，这非常有趣:

Processing XML Showplans Using SQLCLR in SQL Server 2005