[DMV] 실행계획에 사용되고 있는 물리연산자 살펴보기

일전에 2013/04/15 - [DMV] 특정 프로시저의 실행계획이 바뀌었다? 글을 통해서 소개도 했었고, 2013.05.04 DBNuri Study 에서도 잠깐 보여드렸습니다. 특정 프로시저가 재 컴파일이 되면서 실행계획에 문제가 발생하면 CPU 사용률이 급격히 올라가기에 이 현상을 모니터링 하기 위해서 만들었고, 스크립트는 아래와 같습니다.

 

시작은 이랬습니다. DMV로 실행계획을 볼수 있고, 해당 실행계획은 XML로 되어 있으니 해당 XML에 있는 물리적 연산자를 몽땅 추출하여 내가 원하지 않는 연산자가 있는지 없는지를 알면 되지 않을까?

SELECT

        -- d.plan_handle ,

        OBJECT_NAME(b.objectid, b.dbid) [SP명] ,

        c.query_plan AS [프로시저 전체 실행계획]

        -- d.usecounts AS [실행계획이 만들어 진 이후로 사용된 횟수] ,

        -- b.text AS [프로시저 내용] ,

        --'EXEC ' + DB_NAME( b. dbid ) + '.DBO.sp_recompile ' + OBJECT_NAME ( b. objectid, b.dbid ) AS [ReCompile] ,

        --'DBCC FreeProcCache (' + sys. fn_varbintohexstr( d.plan_handle ) + ')' AS [실행계획 삭제]

FROM    sys.dm_exec_cached_plans AS d

        CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS b

        CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) AS c

WHERE   OBJECT_NAME(b.objectid, b.dbid) = 'usp_Disp_SelectCategorySumGoodsList_v3'

        AND b.text NOT LIKE '%sys.dm_exec_cached_plans%'

 

그럼 일단, 플랜을 저장하고 XML을 Query로 이용해서 물리연산자를 추출해 보자라고 생각했죠.

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

*   쿼리 플랜을 저장한다.

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

IF OBJECT_ID('tempdb..#AT_QueryPlanXml') IS NOT NULL

    BEGIN

        DROP TABLE #AT_QueryPlanXml

    END ;

 

-- 임시테이블을 만든다.

CREATE TABLE #AT_QueryPlanXml ( spName VARCHAR(500) ,

                                spPlan XML )

 

-- 특정 저장프로시저의 플랜을 저장한다.

-- 이왕 이면 주의대상 저장프로시저를 저장

INSERT  INTO #AT_QueryPlanXml

        ( spName ,

          spPlan )

SELECT

        -- d.plan_handle ,

        OBJECT_NAME(b.objectid, b.dbid) [SP명] ,

        c.query_plan AS [프로시저 전체 실행계획]

        -- d.usecounts AS [실행계획이 만들어 진 이후로 사용된 횟수] ,

        -- b.text AS [프로시저 내용] ,

        --'EXEC ' + DB_NAME( b. dbid ) + '.DBO.sp_recompile ' + OBJECT_NAME ( b. objectid, b.dbid ) AS [ReCompile] ,

        --'DBCC FreeProcCache (' + sys. fn_varbintohexstr( d.plan_handle ) + ')' AS [실행계획 삭제]

FROM    sys.dm_exec_cached_plans AS d

        CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS b

        CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) AS c

WHERE   OBJECT_NAME(b.objectid, b.dbid) = 'usp_Disp_SelectCategorySumGoodsList_v3'

        AND b.text NOT LIKE '%sys.dm_exec_cached_plans%'

 

 

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

*   저장된 실행계획의 물리연산자를 추출한다.

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

;WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

SELECT

        stmt.stmt_details.value( '@PhysicalOp', 'nvarchar(max)'),

        stmt.stmt_details.value( '@EstimateRows', 'nvarchar(max)')

FROM

        (

            SELECT  spPlan

            FROM    #AT_QueryPlanXml

        ) AS Result

CROSS APPLY spPlan.nodes('//sp:RelOp' ) AS stmt (stmt_details)

 

보시는봐와 같이 해당 프로시져 플랜의 연산자는 총 83개 중에 간간히 Clustered Index Scan 연산자를 이용하여 40만건이 넘는 행수를 읽고 있는게 보입니다. 그럼 Clustered Index Scan 이던 Table Scan 이던 해당 플랜 안에 Scan이 몇 개나 있고 그 예상행수는 얼마나 되는지를 판단하기 위하여 아래와 같은 최종 형태의 스크립트를 실행하시면 ...

USE DBA

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

/*--------------------------------------------------------------------------------------------

SP명 : DBA.FN_Query_PlanOp

작성자 : 이 승연

작성일 :

설명 : XML 안에 어떤 물리적연산이 있는지 확인

입력변수:

출력변수:

사용방법:

--------------------------------------------------------------------------------------------

작성자          작성일자         설명

--------------------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------------------*/

CREATE FUNCTION DBO.FN_Query_PlanOp ( @QueryXML XML )

RETURNS VARCHAR(MAX)

AS

BEGIN

 

    DECLARE @Result VARCHAR(MAX) = '' ;

    DECLARE @TMP_Result VARCHAR(MAX) = '' ;

    DECLARE @Tmp_QueryXML TABLE ( ShowPlan XML ) ;

    DECLARE @Tmp_QueryXMLOp TABLE ( PhysicalOp VARCHAR(500) ,

                                    EstimateRows FLOAT ) ;

    --SELECT @Result = STR(@statement_start_offset) + ' : ' + CAST(@QueryXML AS VARCHAR(MAX))

 

    INSERT  INTO @Tmp_QueryXML

    VALUES  ( @QueryXML ) ;  

 

 

    WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

    INSERT INTO @Tmp_QueryXMLOp

    SELECT

            stmt.stmt_details .value ( '@PhysicalOp' , 'nvarchar(max)' ) AS PhysicalOp ,

            stmt.stmt_details .value ( '@EstimateRows' , 'nvarchar(max)' ) AS EstimateRows

    FROM    (

                SELECT  *

                FROM    @Tmp_QueryXML

            ) AS Result

            CROSS APPLY ShowPlan. nodes( '//sp:RelOp' ) AS stmt ( stmt_details)

 

  

 

    SELECT  @TMP_Result = @TMP_Result + ', (' + PhysicalOp + '/'

            + CAST (COUNT(PhysicalOp) AS VARCHAR(100)) + '/'

            + REPLACE(CONVERT (VARCHAR, CONVERT (MONEY, AVG(EstimateRows)), 1),

                      '.00', '') + ')'

    FROM    @Tmp_QueryXMLOp

    WHERE   PhysicalOp LIKE '%SCAN%'

    GROUP BY PhysicalOp

 

    SELECT  @Result = CASE WHEN @TMP_Result = '' THEN '-'

                           ELSE RIGHT(@TMP_Result, LEN(@TMP_Result) - 1)

                      END

  

        -- Return the result of the function

    RETURN @Result

END

GO

 

 

 

-- 위 스칼라 함수 생성 후 아래와 같은 쿼리를 실행한다.

DECLARE @sp_Name VARCHAR(MAX) = '저장프로시저' ;

DECLARE @Query_Plan TABLE ( [plan_handle] [varbinary](64) NOT NULL ,

                            [SP명] [nvarchar](128) NULL ,

                            [프로시저 전체 실행계획] [xml] NULL ,

                            [실행계획이 만들어 진 이후로 사용된 횟수] [int] NOT NULL ,

                            [프로시저 내용] [nvarchar](MAX) NULL ,

                            [ReCompile] [nvarchar](279) NULL ,

                            [실행계획 삭제] [nvarchar](MAX) NULL ,

                            [statement_start_offset] [int] NOT NULL ,

                            [statement_end_offset] [int] NOT NULL ,

                            [ShowPlan] [xml] NULL ) ;

WITH    A AS ( SELECT   d.plan_handle ,

                        OBJECT_NAME(b.objectid, b.dbid) [SP명] ,

                        c.query_plan AS [프로시저 전체 실행계획] ,

                        d.usecounts AS [실행계획이 만들어 진 이후로 사용된 횟수] ,

                        b.text AS [프로시저 내용] ,

                        'EXEC ' + DB_NAME(b.dbid) + '.DBO.sp_recompile '

                        + OBJECT_NAME(b.objectid, b.dbid) AS [ReCompile] ,

                        'DBCC FreeProcCache ('

                        + sys.fn_varbintohexstr(d.plan_handle) + ')' AS [실행계획 삭제]

               FROM     sys.dm_exec_cached_plans AS d

                        CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS b

                        CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) AS c

               WHERE    OBJECT_NAME(b.objectid, b.dbid) = @sp_Name

                        AND b.text NOT LIKE '%sys.dm_exec_cached_plans%'

             ),

        B AS ( SELECT   A.plan_handle ,

                        A.[SP명] ,

                        A.[프로시저 전체 실행계획] ,

                        A.[실행계획이 만들어 진 이후로 사용된 횟수] ,

                        A.[프로시저 내용] ,

                        A.[ReCompile] ,

                        A.[실행계획 삭제] ,

                        qs.statement_start_offset ,

                        qs.statement_end_offset

               FROM     A AS A

                        INNER JOIN sys.dm_exec_query_stats AS qs

                            ON A.plan_handle = qs.plan_handle

             )

    INSERT  INTO @Query_Plan

            SELECT  B.* ,

                    CAST(qp.query_plan AS XML) AS [ShowPlan]

            FROM    B

                    CROSS APPLY sys.dm_exec_text_query_plan(B.plan_handle,

                                                            B.statement_start_offset,

                                                            B.statement_end_offset)

                    AS qp

 

 

SELECT  DBA.DBO.FN_Query_PlanOp(ShowPlan) AS [물리적연산] ,

        [SP명] ,

        [프로시저 전체 실행계획] ,

        [실행계획이 만들어 진 이후로 사용된 횟수] ,

        [프로시저 내용] ,

        [ReCompile] ,

        [실행계획 삭제] ,

        statement_start_offset ,

        statement_end_offset ,

        [ShowPlan]

FROM    @Query_Plan

 

해당 프로시저에 Clustered Index Scan 이 4건이 있으며 그 4건에 대한 평균 예상행수는 20만건이 있다고 출력을 해주고 있습니다. 그럼 이제 정상적인 플랜을 미리 저장해 두고 해당 스크립트로 현재 돌아가고 있는 플랜의 연산자를 추출하여 비교하면 어떤 연산자가 새로 생겼는지 기존에는 어떻게 돌고 있다가 재 컴파일 되면서 지금은 이렇게 돌고 있구나 라고 판단하실 수 있을 겁니다.