使用C#.NET监控SQLServer SQL执行计划突变

使用动态管理视图（DMVs）来检测SQL执行计划的突变

我们需要关注哪些能够提供查询执行统计和计划信息的视图。以下是一些可以用于此目的的DMVs以及相应的查询示例：

  1. sys.dm_exec_query_stats：这个视图提供了关于SQL Server中查询执行的统计信息，包括CPU时间、总工作时间、执行次数等。
SELECT sql_handle, statement_start_offset, statement_end_offset, creation_time, last_execution_time, execution_count, total_worker_time, total_elapsed_time FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC;
  1. sys.dm_exec_sql_text：结合sys.dm_exec_query_stats使用，可以获取与sql_handle对应的SQL文本。
SELECT qs.sql_handle, st.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st;
  1. sys.dm_exec_cached_plans：这个视图包含了缓存中的执行计划的统计信息，如计划的大小、执行次数等。
SELECT cacheobjtype, usecounts, cacheobjtype, objtype, size_in_bytes, creation_time, last_use_time FROM sys.dm_exec_cached_plans ORDER BY usecounts DESC;
  1. sys.dm_exec_query_plan：这个视图与sys.dm_exec_sql_text结合使用，可以获取特定查询的执行计划。
SELECT qp.query_plan, st.text FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;

要检测执行计划的突变，可以定期运行上述查询，并将结果存储在表中，或者与之前的结果进行比较。

例如，可以比较两个不同时间点的total_worker_time或total_elapsed_time，以查看是否有显著变化，这可能表明执行计划已经改变。

由于sys.dm_exec_query_stats中的信息可能会被SQL Server周期性地重置，因此最好的做法是将这些信息记录到一个持久化表中，这样可以基于历史数据来检测突变。

此外，执行计划的突变可能需要结合多个DMVs的信息来综合判断。

使用C#.NET实现以上方案

1. 创建持久化表

首先，在SQL Server中创建一个表来存储查询统计信息。这个表将用于存储从DMVs中获取的数据。

CREATE TABLE QueryExecutionStats (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    SqlHandle VARBINARY(64),
    StatementStartOffset INT,
    StatementEndOffset INT,
    CreationTime DATETIME,
    LastExecutionTime DATETIME,
    ExecutionCount BIGINT,
    TotalWorkerTime BIGINT,
    TotalElapsedTime BIGINT,
    SqlText NVARCHAR(MAX),
    QueryPlan XML,
    RecordedTime DATETIME DEFAULT GETDATE()
);

2. C# 实现代码

以下C#代码展示了如何连接到SQL Server，执行DMV查询，并将结果存储在持久化表中。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

class Program
{
    static string connectionString = "YourConnectionStringHere";

    static void Main(string[] args)
    {
        try
        {
            // 记录查询统计信息
            RecordQueryExecutionStats();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
        }
    }

    static void RecordQueryExecutionStats()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // 查询sys.dm_exec_query_stats和sys.dm_exec_sql_text
            string query = @"
                SELECT 
                    qs.sql_handle, 
                    qs.statement_start_offset, 
                    qs.statement_end_offset, 
                    qs.creation_time, 
                    qs.last_execution_time, 
                    qs.execution_count, 
                    qs.total_worker_time, 
                    qs.total_elapsed_time, 
                    st.text AS SqlText,
                    qp.query_plan AS QueryPlan
                FROM 
                    sys.dm_exec_query_stats AS qs
                CROSS APPLY 
                    sys.dm_exec_sql_text(qs.sql_handle) AS st
                CROSS APPLY 
                    sys.dm_exec_query_plan(qs.plan_handle) AS qp
                ORDER BY 
                    qs.total_worker_time DESC;";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // 读取查询结果
                        var sqlHandle = (byte[])reader["sql_handle"];
                        var statementStartOffset = (int)reader["statement_start_offset"];
                        var statementEndOffset = (int)reader["statement_end_offset"];
                        var creationTime = (DateTime)reader["creation_time"];
                        var lastExecutionTime = (DateTime)reader["last_execution_time"];
                        var executionCount = (long)reader["execution_count"];
                        var totalWorkerTime = (long)reader["total_worker_time"];
                        var totalElapsedTime = (long)reader["total_elapsed_time"];
                        var sqlText = reader["SqlText"].ToString();
                        var queryPlan = reader["QueryPlan"].ToString();

                        // 将结果插入到持久化表中
                        InsertQueryExecutionStats(sqlHandle, statementStartOffset, statementEndOffset, creationTime, lastExecutionTime, executionCount, totalWorkerTime, totalElapsedTime, sqlText, queryPlan);
                    }
                }
            }
        }
    }

    static void InsertQueryExecutionStats(byte[] sqlHandle, int statementStartOffset, int statementEndOffset, DateTime creationTime, DateTime lastExecutionTime, long executionCount, long totalWorkerTime, long totalElapsedTime, string sqlText, string queryPlan)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string insertQuery = @"
                INSERT INTO QueryExecutionStats (
                    SqlHandle, 
                    StatementStartOffset, 
                    StatementEndOffset, 
                    CreationTime, 
                    LastExecutionTime, 
                    ExecutionCount, 
                    TotalWorkerTime, 
                    TotalElapsedTime, 
                    SqlText, 
                    QueryPlan
                ) VALUES (
                    @SqlHandle, 
                    @StatementStartOffset, 
                    @StatementEndOffset, 
                    @CreationTime, 
                    @LastExecutionTime, 
                    @ExecutionCount, 
                    @TotalWorkerTime, 
                    @TotalElapsedTime, 
                    @SqlText, 
                    @QueryPlan
                );";

            using (SqlCommand command = new SqlCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@SqlHandle", sqlHandle);
                command.Parameters.AddWithValue("@StatementStartOffset", statementStartOffset);
                command.Parameters.AddWithValue("@StatementEndOffset", statementEndOffset);
                command.Parameters.AddWithValue("@CreationTime", creationTime);
                command.Parameters.AddWithValue("@LastExecutionTime", lastExecutionTime);
                command.Parameters.AddWithValue("@ExecutionCount", executionCount);
                command.Parameters.AddWithValue("@TotalWorkerTime", totalWorkerTime);
                command.Parameters.AddWithValue("@TotalElapsedTime", totalElapsedTime);
                command.Parameters.AddWithValue("@SqlText", sqlText);
                command.Parameters.AddWithValue("@QueryPlan", queryPlan);

                command.ExecuteNonQuery();
            }
        }
    }
}

3. 定期运行和比较

你可以将此代码设置为定期运行（例如，使用Windows任务计划程序或后台服务），并将结果存储在持久化表中。之后，可以通过比较不同时间点的total_worker_time、total_elapsed_time等字段来检测执行计划的突变。

4. 检测突变

为了检测执行计划的突变，你可以编写SQL查询来比较历史数据，例如：

SELECT 
    a.SqlText, 
    a.TotalWorkerTime AS PreviousWorkerTime, 
    b.TotalWorkerTime AS CurrentWorkerTime, 
    a.TotalElapsedTime AS PreviousElapsedTime, 
    b.TotalElapsedTime AS CurrentElapsedTime
FROM 
    QueryExecutionStats a
JOIN 
    QueryExecutionStats b ON a.SqlHandle = b.SqlHandle 
    AND a.StatementStartOffset = b.StatementStartOffset 
    AND a.StatementEndOffset = b.StatementEndOffset
WHERE 
    a.RecordedTime = '2023-01-01 00:00:00' 
    AND b.RecordedTime = '2023-01-02 00:00:00'
    AND (ABS(a.TotalWorkerTime - b.TotalWorkerTime) > 1000 
         OR ABS(a.TotalElapsedTime - b.TotalElapsedTime) > 1000);

这个查询将比较两个时间点的执行统计信息，并找出那些total_worker_time或total_elapsed_time有显著变化的查询。

通过上述C#代码和SQL查询，定期捕获SQL Server中的查询执行统计信息，并检测执行计划的突变。

 

周国庆

2025年1月9日

posted @ 2024-05-13 08:45  Eric zhou  阅读(1210)  评论(3)    收藏  举报