C#监控长查询及终止方法详解

using System;
using System.Data.SqlClient;

string connectionString = "your_connection_string";

void CheckLongRunningQueries()
{
    string sql = @"
        SELECT session_id, 
               start_time, 
               command, 
               DATEDIFF(SECOND, start_time, GETDATE()) AS duration_seconds,
               text
        FROM sys.dm_exec_requests
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)
        WHERE status = 'running' 
          AND DATEDIFF(SECOND, start_time, GETDATE()) > 30";
    
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand(sql, conn))
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int sessionId = reader.GetInt32("session_id");
                    string command = reader.GetString("command");
                    int duration = reader.GetInt32("duration_seconds");
                    string sqlText = reader["text"].ToString();

                    Console.WriteLine($"长时间运行查询: SPID={sessionId}, 命令={command}, 时长={duration}秒");
                    Console.WriteLine($"SQL: {sqlText}");
                }
            }
        }
    }
}

void KillQuery(int sessionId)
{
    string killSql = $"KILL {sessionId}";

    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand(killSql, conn))
        {
            try
            {
                cmd.ExecuteNonQuery();
                Console.WriteLine($"已终止会话 {sessionId}");
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"终止失败: {ex.Message}");
            }
        }
    }
}

var timer = new System.Timers.Timer(10000); // 每10秒检查一次
timer.Elapsed += (sender, e) => {
    CheckLongRunningQueries(); // 可在此加入自动kill逻辑
};
timer.Start();