SQL Server에서 블로킹이 발생한 세션을 모니터링하고 처리하는 방법을 설명합니다. 다음 세 가지 주요 내용을 다룹니다: 블로킹 및 블로킹된 세션 조회, 구체적인 SQL 문 찾기, 그리고 C# 프로그램을 이용한 자동 모니터링 및 처리.

1. 블로킹 및 블로킹된 세션 조회

다음 쿼리는 현재 블로킹된 세션과 블로킹을 유발한 세션의 정보를 반환합니다.

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;

2. 블로킹을 유발한 구체적인 SQL 문 찾기

이 쿼리는 블로킹 세션과 관련된 구체적인 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;

3. C# 프로그램을 활용한 자동 모니터링 및 처리

다음 C# 코드는 10초마다 SQL Server의 블로킹 세션을 확인하고, 루트 블로킹 세션을 종료하며 로그를 기록합니다.

using System;
using System.Data.SqlClient;
using System.IO;
using System.Timers;

class SessionMonitor
{
    private static Timer timer;
    private static string connectionString = "your_connection_string_here";

    static void Main(string[] args)
    {
        timer = new Timer(10000); // 10 seconds interval
        timer.Elapsed += CheckForBlockingSessions;
        timer.AutoReset = true;
        timer.Enabled = true;

        Console.WriteLine("Press [Enter] to exit.");
        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 명령 실행 권한이 필요합니다.
• 프로덕션 환경에서 사용하기 전에 충분히 테스트하세요.
• 로그 파일의 경로와 권한을 실제 환경에 맞게 설정해야 합니다.