MSSQL 최적화, Block 하고 있는 쿼리 찾기(병목현상을 잡아라)

시나리오

회사의 ERP는 MSSQL 기반의 더존 ERP를 사용하고 있다.

사용 기간이 오래 되다보니 데이터량도 많아지고 접속자, 요청 쿼리 등도 많아졌다.

그러다 보니 당연한 것이겠지만 간혹 특정 시간대에 ERP 실행 속도가 느려져서, 수주 등록이 안되거나, 데이터 조회가 안 되는 경우가 발생했다.

말그대로 "ERP가 느리다", "접속이 안된다", "실행 속도가 갑자기 느려졌다" 아우성이다.

특정 시간대라 함은 어느 부서에서 어떤 ERP 모듈을 실행으로 인해 MSSQL에 부하(병목현상)을 유발하게 된 것이다.

그 모듈을 찾아서 사용된 쿼리, 프로시저, 테이블 등을 최적화 해야 한다.

그럼 어떻게 찾을 것인가?

시간대를 알기에 부서별로 확인해서 어느 모듈인지 추적은 가능하지만, 단순히 그 모듈이라고 특정하기에는 너무 인간적이다.

어떻게 하면 논리적으로 병목현상, MSSQL 쿼리 실행 속도를 느리게 하는 범인, ERP모듈(쿼리문)을 찾아서 특정할 수 있을까?

힌트는 sp_who

구글링 해보니 sp_who 라는 것이 있었어 MSSQL에서 해보니 프로시저가 없다고 나왔다.

다시 찾아 보니 개발자들이 제작한 sp_who2가 있는데 개발자 별로 다양하게 다양한 형태로 짜여 있어서 블로그와 커뮤니티를 참고하여 프로시저를 만들어 보았다.

ERP가 느리다고 얘기가 나오면 프로시저를 실행해서 CPUTime이나 DiskIO 값이 높은 세션은 kill 해버렸다.

하지만 이것은 근본적으로 해결 방법이 안된다.

어느 모듈에서 트렌잭션을 잡고 있어서 병목현상이 발생하는지, 그 모듈을 찾아서 쿼리나 테이블을 최적화 해야 해결될 문제이다.

범인을 찾아라 sp_who3

처음에는 blk가 무엇인지 몰랐다. 사실 지금도 자세히는 모르겠다.

쿼리 실행 후 트랜젝션을 수행하는 동안 해당 테이블을 다른 쿼리가 사용하지 못하게 block 시킨 세션아이디로 알고 있다.

그래서 일정 간격으로 sp_who 결과 값을 로그로 기록하되 좀더 상세한 정보(실행시간, 지연시간, 대기시간 등)를 기록하고 싶어서 검색을 해보니 sp_who3 이란 것이 있었다. (구글 신과 블로거들에게 감사할 따름이다)

아래는 지금 적용해서 사용중인 sp_who3 프로시저이다.

실행을 하게 되면 실행시간(ElapsedMS) 와 대기시간(WaitTime)이 1분 이상인 것들을 찾고 대기하게 만든 쿼리 세션 아이디(BlkBy, BlockBySessid)를 찾아서 BlkBy의 쿼리문(프로시저)과 테이블을 최적화하면 된다.

또 BlkBy에 자주 걸리는 쿼리를 찾으면 바로 그놈이 범인인것이다.

USE [master];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[sp_who3]
@SessionID int = null
WITH EXEC AS CALLER
AS
BEGIN
    set nocount on
    SELECT    
        SPID = er.session_id 		-- 세션아이디
        ,Status = ses.status		-- 상태
        ,[Login] = ses.login_name	-- 디비 서버 접속자 명
        ,Host = ses.host_name		-- 디비 서버 접속 디바이스 호스트명(컴퓨터이름)
        ,BlockBySessId = er.blocking_session_id	-- 현재 세션이 지연되게 만든 세션 아이디(BlkBy 또는 Blk 라고도 한다)
        ,DBName = DB_Name(er.database_id)	-- 디비명
        ,CommandType = er.command	-- 쿼리 유형(SELECT,UPDATE 등)
        ,SQLStatement =
            SUBSTRING(
                qt.text,
                er.statement_start_offset/2,
                (CASE WHEN er.statement_end_offset = -1
                    THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 4
                    ELSE er.statement_end_offset
                    END - er.statement_start_offset)
                )						-- 실행된 쿼리문
        ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
        	-- 실행된 객체명(프로시저명)
        ,ElapsedMS = er.total_elapsed_time	-- 쿼리실행 시간(microsecond)
        ,CPUTime = er.cpu_time				-- CPU 사용 시간
        ,IOReads = er.logical_reads + er.reads -- 읽기에 사용된 row 수
        ,IOWrites = er.writes				-- 쓰기에 사용된 row 수
        ,LastWaitType = er.last_wait_type	-- 마지막 대기 유형
        ,WaitTime = er.wait_time			-- 실제 실행 시작까지의 대기 시간(microsecond) 
        ,StartTime = er.start_time			-- 실행 시작 시간(문자열 날짜 시간)
        ,Protocol = con.net_transport
        ,transaction_isolation =
            (CASE ses.transaction_isolation_level
                WHEN 0 THEN 'Unspecified'
                WHEN 1 THEN 'Read Uncommitted'
                WHEN 2 THEN 'Read Committed'
                WHEN 3 THEN 'Repeatable'
                WHEN 4 THEN 'Serializable'
                WHEN 5 THEN 'Snapshot'
            END)
        ,ConnectionWrites = con.num_writes
        ,ConnectionReads = con.num_reads
        ,ClientAddress = con.client_net_address	-- 클라이언트 IP
        ,Authentication = con.auth_scheme
    FROM    
    	sys.dm_exec_requests er
        LEFT JOIN sys.dm_exec_sessions ses
        	ON ses.session_id = er.session_id
        LEFT JOIN sys.dm_exec_connections con
        	ON con.session_id = ses.session_id
        CROSS APPLY 
         	sys.dm_exec_sql_text(er.sql_handle) as qt
    WHERE
    	@SessionID IS NULL OR er.session_id = @SessionID
        AND er.session_id > 50
    ORDER BY    
    	er.start_time asc, er.blocking_session_id DESC
            ,er.session_id
END
GO

참고 : docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-who-transact-sql?view=sql-server-ver15

 

이제 이것으로 범인을 찾기 위한 CCTV는 설치했다.

CCTV 영상을 담기 위한 저장소는 따로 작성해보자.