一、查询阻塞和被阻塞的会话
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);
}
}
说明
- 连接字符串：替换
your_connection_string_here为实际的数据库连接字符串。 - 定时器：使用
System.Timers.Timer类设置每10秒执行一次检查。 - 检查阻塞会话：在
CheckForBlockingSessions方法中，查询阻塞会话和根源会话的信息。 - 终止会话：在
KillSession方法中，执行KILL命令来终止阻塞会话。 - 日志记录：日志记录包括阻塞会话的详细信息和终止会话的操作，以及错误信息。
注意事项
- 运行此程序需要确保有足够的权限来访问数据库和执行
KILL命令。 - 请仔细测试程序以确保其符合预期行为，尤其是在生产环境中。
- 日志文件的路径和权限需要根据实际情况进行配置。
到此这篇关于SQLServer如何监控阻塞会话的文章就介绍到这了,更多相关SQLServer阻塞会话内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！
