SQLServer 如何监控阻塞会话

SELECT     r.session_id AS [Blocked Session ID],    r.blocking_session_id AS [Blocking Session ID],    r.wait_type,    r.wait_time,    r.wait_resource,    s1.program_name AS [Blocked Program Name],    s1.login_name AS [Blocked Login],    s2.program_name AS [Blocking Program Name],    s2.login_name AS [Blocking Login],    r.text AS [SQL Text]FROM sys.dm_exec_requests AS rLEFT JOIN sys.dm_exec_sessions AS s1 ON r.session_id = s1.session_idLEFT JOIN sys.dm_exec_sessions AS s2 ON r.blocking_session_id = s2.session_idCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS rWHERE r.blocking_session_id <> 0;

SELECT     r.session_id,    r.blocking_session_id,    t.text AS [SQL Text],    r.wait_type,    r.wait_time,    r.wait_resourceFROM sys.dm_exec_requests AS rCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS tWHERE r.blocking_session_id <> 0;

using System;using System.Data.SqlClient;using System.IO;using System.Timers;
class Program{    private static Timer timer;    private static string connectionString = "your_connection_string_here";
    static void Main(string[] args)    {        timer = new Timer(10000); // 每10秒执行一次        timer.Elapsed += CheckForBlockingSessions;        timer.AutoReset = true;        timer.Enabled = true;
        Console.WriteLine("Press [Enter] to exit the program.");        Console.ReadLine();    }
    private static void CheckForBlockingSessions(object source, ElapsedEventArgs e)    {        try        {            using (SqlConnection connection = new SqlConnection(connectionString))            {                connection.Open();
                string query = @"                SELECT                     r.session_id AS BlockedSessionID,                    r.blocking_session_id AS BlockingSessionID,                    r.text AS SqlText                FROM sys.dm_exec_requests AS r                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS r                WHERE r.blocking_session_id <> 0;";
                using (SqlCommand command = new SqlCommand(query, connection))                {                    using (SqlDataReader reader = command.ExecuteReader())                    {                        while (reader.Read())                        {                            int blockedSessionId = reader.GetInt32(0);                            int blockingSessionId = reader.GetInt32(1);                            string sqlText = reader.GetString(2);
                            LogBlockingSession(blockedSessionId, blockingSessionId, sqlText);                            KillSession(blockingSessionId);                        }                    }                }            }        }        catch (Exception ex)        {            LogError(ex.Message);        }    }
    private static void KillSession(int sessionId)    {        try        {            using (SqlConnection connection = new SqlConnection(connectionString))            {                connection.Open();                string killQuery = $"KILL {sessionId};";                using (SqlCommand killCommand = new SqlCommand(killQuery, connection))                {                    killCommand.ExecuteNonQuery();                    LogKillSession(sessionId);                }            }        }        catch (Exception ex)        {            LogError($"Failed to kill session {sessionId}: {ex.Message}");        }    }
    private static void LogBlockingSession(int blockedSessionId, int blockingSessionId, string sqlText)    {        string logMessage = $"[{DateTime.Now}] Blocked Session ID: {blockedSessionId}, Blocking Session ID: {blockingSessionId}, SQL Text: {sqlText}";        File.AppendAllText("blocking_sessions.log", logMessage + Environment.NewLine);        Console.WriteLine(logMessage);    }
    private static void LogKillSession(int sessionId)    {        string logMessage = $"[{DateTime.Now}] Killed Session ID: {sessionId}";        File.AppendAllText("killed_sessions.log", logMessage + Environment.NewLine);        Console.WriteLine(logMessage);    }
    private static void LogError(string message)    {        string logMessage = $"[{DateTime.Now}] Error: {message}";        File.AppendAllText("errors.log", logMessage + Environment.NewLine);        Console.WriteLine(logMessage);    }}