아직도 꿈만 꿔? :: BlockMon, SQL Server 차단 프로세스 모니터링 도구

2009/09/18 14:16

BlockMon, SQL Server 차단 프로세스 모니터링 도구

2009/09/18 14:16 in 습작/유틸

특정 프로세스에서 리소스를 오랫동안 잠글 경우 다른 프로세스에서 잠긴 리소스를 접근하지 못하는 상황이 발생한다.

로직에 버그가 있어 트랜잭션을 시작하고 롤백이나 커밋을 하지 않게 될 경우 차단이 발생되어 최악의 경우 전체 서비스가 멎는 사태가 발생하게 된다.

로직에 문제가 없다고 하더라도 인덱스가 설정되어 있지 않거나 잘못 설정되어 있어 트랜잭션 수행 시간이 길어지면 차단이 발생할 수 있다.

데드락의 경우에는 SQL Server에서 발생 즉시 프로세스 하나를 희생해서 다른 프로세스에 영향을 주지 않지만 차단의 경우에는 원래 시간이 오래 걸리는 작업인지 문제가 있는 작업인지 SQL Server가 판단할 수 없어 문제가 더 심각해진다.

BlockMon은 이러한 차단을 모니터링하고 차단이 발생했을때 차단된 프로세스와 차단을 유발한 프로세스의 정보와 각기 실행한 SQL 구문을 기록할 수 있는 차단 모니터링 도구이다.

주요기능

모니터링할 SQL Server를 여러개 등록할 수 있고 차단이 발생했을때 차단 정보를 기록한다.

차단 정보는 로그파일에도 기록되며 기록되는 정보는 프로세스의 상세정보와 Input Buffer의 내용(마지막으로 실행한 SQL 구문)도 포함한다.

부가기능

모니터링하고 있는 서버를 더블클릭하면 해당 서버의 현재 실행중인 프로세스리스트를 출력한다.

차단된 프로세스의 경우 붉은 색의 폰트로 강조되고 Input Buffer의 내용과 차단을 유발한 프로세스를 kill 할 수 있는 기능도 있다.

프로그램 소스 살펴보기

BlockMon은 sp_who2 시스템저장프로시저를 사용해서 현재 프로세스정보를 조회한다.

특정 시간간격으로 현재 프로세스정보를 조회하고 차단된 프로세스가 있을 경우 DBCC InputBuffer 구문으로 해당 프로세스의 마지막 SQL 구문을 조회한 후 차단정보를 기록한다.

// 현재 프로세스 조회
public static DataTable GetProcess(SqlConnection conn)
{
    string query = "set language us_english\r\nexec sp_who2";

    using (SqlCommand cmd = new SqlCommand())
    using (SqlDataAdapter da = new SqlDataAdapter())
    {
        cmd.Connection = conn;
        cmd.CommandText = query;
        cmd.CommandType = CommandType.Text;

        da.SelectCommand = cmd;
        DataTable dt = new DataTable();
        da.Fill(dt);

        return dt;
    }
}

“set language us_english”를 사용해 세션 언어설정을 변경한 이유는 sp_who2의 lastbatch 값(시각부분)이 올바르게 출력되도록 하기 위해서이다. (참조 : http://blog.jeidee.net/770 )

// 프로세스의 마지막 수행 SQL 구문
public static void GetInputBuffer(SqlConnection conn, Int16 spid, InputBuffer inputBuffer)
{
    string query = "dbcc inputbuffer(" + spid.ToString() + ")";

    using (SqlCommand cmd = new SqlCommand())
    using (SqlDataAdapter da = new SqlDataAdapter())
    using (DataTable dt = new DataTable())
    {
        cmd.Connection = conn;
        cmd.CommandText = query;
        cmd.CommandType = CommandType.Text;

        da.SelectCommand = cmd;
        da.Fill(dt);

        if (dt.Rows.Count != 1) return;

        inputBuffer.EventType = dt.Rows[0]["EventType"].ToString();
        inputBuffer.Parameters = Convert.ToInt16(dt.Rows[0]["Parameters"]);
        inputBuffer.EventInfo = dt.Rows[0]["EventInfo"].ToString();
    }
}