DMV를 이용하여 현재 실행되고 있는 쿼리문 보기

2013.05.04 DBNuri Study 에서 발표하였던 HeavyQuery & LongTime Query 중 하나이며 제가 평소에도 많이 사용하는 Query이며 DB Server에 문제가 생겼을 때나 생기기 전에 해당 Query를 통하여 어느정도 분석이 가능하며 해당 Query를 면밀하게 보다 보면 실행에 문제가 되는 것들이 매트릭스 처럼 눈에 착 ~ !! 와닿는 Query 입니다. 공유되는 Query을 응용하시면 많은 내용을 보실 수 있는 가장 기본이 되는 Query 입니다.

 

USE DBA ;

GO

 

/********************************************************************************

*   스칼라 함수 추가

*   현재 서버에서 실행되고 있는 쿼리

********************************************************************************/

CREATE FUNCTION dbo.AFN_THREEPARTNAME(

    @object_id INT,

    @database_id INT

)

RETURNS NVARCHAR(1000)

AS

BEGIN

    RETURN (QUOTENAME(DB_NAME(@database_id)) + '.'

          + QUOTENAME(OBJECT_SCHEMA_NAME(@object_id, @database_id))

          + '.' + QUOTENAME(OBJECT_NAME(@object_id, @database_id)));

END

 

먼저 [데이터베이스].[스키마].[오브젝트명]을 만들어 주는 스칼라 함수를 만들어 주셔야 합니다.

 

/********************************************************************************

*    현재 서버에서 실행되고 있는 쿼리 (sys.sysprocesses 추가)

********************************************************************************/

SELECT  r.session_id AS [세션ID] ,

        r.[status] AS [상태],

        r.wait_type AS [대기상태],

        r.scheduler_id AS [SchedulerID],

        CASE WHEN qt.objectid IS NULL THEN 'AD-HOC'

             ELSE dbo.AFN_THREEPARTNAME(qt.objectid, qt.dbid)

        END AS [SP이름] ,

        SUBSTRING(qt.[text], r.statement_start_offset / 2,

                  ( CASE WHEN r.statement_end_offset = -1

                         THEN LEN(CONVERT (NVARCHAR(MAX), qt.[text])) * 2

                         ELSE r.statement_end_offset

                    END - r.statement_start_offset ) / 2) AS [statement_executing] ,

        DB_NAME(qt.[dbid])AS [DatabaseName] ,

        OBJECT_NAME(qt.objectid) AS [ObjectName] ,

        r.cpu_time / 1000. AS [CPU시간(s)],

        r.logical_reads / 128. AS [논리적읽기(MB)] ,

        r.granted_query_memory / 128. AS [쿼리메모리(MB)],

        r.start_time ,

        r.total_elapsed_time / 1000. AS [실행되고있는시간(s)] ,

        ( r.total_elapsed_time / 1000. ) / 60 AS [실행되고있는시간(m)] ,

        r.reads ,

        r.writes ,

        r.plan_handle ,

        (SELECT CAST(qp.query_plan AS XML) FROM sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) AS qp) AS [실행계획보기],

        sp.*

FROM    sys.dm_exec_requests AS r

        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt

        INNER JOIN sys.sysprocesses AS sp

            ON r.session_id = sp.spid

WHERE   r.session_id > 50

       --AND r.session_id != @@SPID

ORDER BY r.total_elapsed_time DESC ,

        r.scheduler_id ,

        r.[status] ,

        r.session_id ;

 

해당 쿼리를 실행하시게 되면 현재 쿼리의 대기상태, 실행되고 있는 시간, 쿼리 메모리, 논리 읽기 용량, CPU 사용량, 실행계획 등 많은 정보를 보실 수 있습니다. 아래의 스크린샷은 제가 실제로 관리하고 있는 메인 서버에서 캡쳐를 한 것 입니다.