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 r LEFT JOIN sys.dm_exec_sessions AS s1 ON r.session_id = s1.session_id LEFT JOIN sys.dm_exec_sessions AS s2 ON r.blocking_session_id = s2.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS r WHERE r.blocking_session_id <> 0;

二、找出阻塞的具体SQL

SELECT      r.session_id,     r.blocking_session_id,     t.text AS [SQL Text],     r.wait_type,     r.wait_time,     r.wait_resource FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE r.blocking_session_id <> 0;

三、编写C#程序，每隔10秒监控SQL Server数据库中的阻塞会话，定位出阻塞的根源会话并终止它们，同时记录日志。

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

说明

1. 连接字符串：替换 your_connection_string_here 为实际的数据库连接字符串。
2. 定时器：使用 System.Timers.Timer 类设置每10秒执行一次检查。
3. 检查阻塞会话：在 CheckForBlockingSessions 方法中，查询阻塞会话和根源会话的信息。
4. 终止会话：在 KillSession 方法中，执行 KILL 命令来终止阻塞会话。
5. 日志记录：日志记录包括阻塞会话的详细信息和终止会话的操作，以及错误信息。

注意事项

• 运行此程序需要确保有足够的权限来访问数据库和执行 KILL 命令。
• 请仔细测试程序以确保其符合预期行为，尤其是在生产环境中。
• 日志文件的路径和权限需要根据实际情况进行配置。

周国庆

2024/5/28