MS-SQL CPU 사용량이 높은 쿼리문 확인

MS-SQL DB 를 이용하는 서버의 CPU 사용량이 100% 가 출력되어 점검을 진행했다.

윈도우에서 성능 모니터에 카운터 추가를 해서 했는데 손이 너무 많이 간다. 

성능 모니터에서 카운터 추가

성능 개체 : Thread

카운터 : % Processor Time / ID Thread 

인스턴스 : sqlservr 리스트 전체 선택

CPU 사용량이 높은 SQL 스레드 ID 확인 : 4280

스레드 ID 로 spid 확인 

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=4280

SPID : 240

spid 로 쿼리문 확인 

dbcc inputbuffer (240)

이렇게 했는데, 일을 두 번 할 필요는 없으니깐, SSMS 에서 한 번에 확인이 가능한 방법을 검색해서 정리해 보았다. 

SSMS 에 접속, tempdb 에서 새쿼리 실행 후 진행을 한다. 

그러면 tempdb 의 임시 테이블에 해당 테이블이 저장이 되는데, 다 확인하고 나면 맨 하단 drop 명령어로 테이블 제거를 하면 된다. 

use tempdb

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),

      Login  VARCHAR(255),HostName  VARCHAR(255),

      BlkBy  VARCHAR(255),DBName  VARCHAR(255),

      Command VARCHAR(255),CPUTime INT,

      DiskIO INT,LastBatch VARCHAR(255),

      ProgramName VARCHAR(255),SPID2 INT,

      REQUESTID INT)

INSERT INTO #sp_who2 EXEC sp_who2

SELECT      *

FROM        #sp_who2

-- Add any filtering of the results here :

WHERE       Status = 'RUNNABLE'

-- Add any sorting of the results here :

ORDER BY    CPUTime ASC

CPU 사용량이 높은 spid 가 확인 됐으면, dbcc inputbuffer (SPID) 를 실행하여 쿼리문을 확인하면 된다. 

다 완료 되었으면 아래의 명령어를 통해 임시 테이블을 제거 한다. 

DROP TABLE #sp_who2

# 현재 실행 중인 쿼리문 확인하는 명령어

SELECT

   sqltext.TEXT,

   req.session_id,

   req.status,

   req.command,

   req.cpu_time,

   req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 

위에 생성 된 임시테이블의 select 구문과 현재 실행 중인 쿼리문을 조회하는 실행문을 동시에 실행해서 일치하는 spid 를 찾는 것도 좋은 방법일 것 같다.