有没有办法以编程方式执行一个带有“Include Actual Execution Plan”选项的查询，并查看是否有任何索引建议？

7 浏览
0 Comments

有没有办法以编程方式执行一个带有“Include Actual Execution Plan”选项的查询，并查看是否有任何索引建议？

我有相当多的查询，并且我想使用SQL Server管理工具的“包括实际执行计划”功能来测试每个查询。

然而，对于100万个以上的查询，我无法手动执行测试。

因此，我想知道是否可以通过C#以编程方式执行它们，并使用“包括实际执行计划”功能，查看SQL Server是否建议任何索引。

图片链接：https://i.stack.imgur.com/GLhz6.png

0

1 答案

0 Comments

文章标题：如何通过程序执行查询以获取实际执行计划并查看是否有索引建议

在编写代码之前，建议先使用Database Engine Tuning Adviser (DTA)来获取所有查询的建议索引、统计信息以及其他有助于查询计划的信息。DTA可以处理大量查询并告诉你可能的索引和统计信息。

如果还想通过代码获取实际执行计划并查看是否有索引建议，需要在连接开始时添加"SET STATISTICS XML ON"语句。这将返回基于该语句的XML数据，可以在第一行的第一列中获取计划的XML。以下是一个快速而简单的函数示例：

private static string GetXmlPlanForQuery(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 as string;
            }
        }
    }
    return result;
}

这是我在一个本地数据库上运行的查询"select TOTAL_SALES from clients where ACTIVE = 0;"的返回XML：



  
    
      
        
          
          
            
              
                
                  
                    
                  
                  
                    
                  
                
              
            
            ...

如果导航到XML中列出的命名空间，可以获取到XML格式的.xsd文件。然后可以在开发者命令提示符中使用xsd showplanxml.xsd /classes命令生成一个showplanxml.cs文件，然后可以使用XmlSerializer对其进行反序列化。

以下是一个小例子，它在缺少索引时会在调试器中断点：

static void Main(string[] args)
{
    string result = GetXmlPlanForQuery("select TOTAL_SALES from clients where ACTIVE = 0;");
    XmlSerializer ser = new XmlSerializer(typeof(ShowPlanXML));
    var plan = (ShowPlanXML)ser.Deserialize(new StringReader(result));
    var missingIndexes =
        plan.BatchSequence.SelectMany(x => x)
            .SelectMany(x => x.Items)
            .OfType()
            .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();
}

以上是通过XmlSerializer反序列化，也可以将其加载到XDocument中，然后使用XPath查找所有名为MissingIndex的节点。

最后，我建议不要使用这种方法，而是编写一个包含所有查询的.sql脚本，或者获取一个包含所有查询的跟踪文件，然后在Database Tuning Engine Adviser上运行它。

0