캐쉬된 실행 계획 보기(Investigating the plan cache)

성능의 이슈가 발생해서 캐쉬에 올라간 실행계획을 확인하고 싶습니다.

 

1. 캐쉬된 실행계획 보기

전체 캐쉬된 실행계획 보기

WITH XMLNAMESPACES(DEFAULT 
        'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
        PLANDMISSINGINDEXES
AS(
        SELECT QUERY_PLAN,
               CP.USECOUNTS
        FROM sys.dm_exec_cached_plans CP
        OUTER APPLY sys.dm_exec_query_plan(CP.PLAN_HANDLE) TP
        WHERE TP.QUERY_PLAN.exist('//MissingIndex')=1
)
SELECT
        STMT.value('(//MissingIndex/@Database)[1]','sysname') 
                AS DATABASE_NAME,
        STMT.value('(//MissingIndex/@Scema)[1]','sysname') 
                AS [SCHEMA_NAME],
        STMT.value('(//MissingIndex/@Table)[1]','sysname') 
                AS [TABLE_NAME],
        STMT.value('(@StatementText)[1]','VARCHAR(4000)') 
                AS SQL_TEXT,
        PMI.USECOUNTS,
        STMT.value('(//MissingIndexGroup/@Impact)[1]','FLOAT') 
                AS IMPACT,
        STMT.query('for $group in //ColumnGroup
               for $column in $group/Column
               where $group/@Usage="EQUALITY"
               return string ($column/@Name)').value('.','varchar(max)') 
               AS EQUALITY_COLUMNS,
        STMT.query('for $group in //ColumnGroup
               for $column in $group/Column
               where $group/@Usage="INEQUALITY"
               return string ($column/@Name)').value('.','varchar(max)') 
               AS INEQUALITY_COLUMNS,
        STMT.query('for $group in //ColumnGroup
               for $column in $group/Column
               where $group/@Usage="INCLUDE"
               return string ($column/@Name)').value('.','varchar(max)') 
               AS INCLUDE_COLUMNS,
        PMI.QUERY_PLAN
FROM PLANDMISSINGINDEXES PMI
CROSS APPLY pmi.query_plan.nodes('//StmtSimple') AS P(STMT)
ORDER BY 
        STMT.value('(//MISSINGINDEXGROUP/@IMPACT)[1]','FLOAT') DESC

특정 인덱스를 사용하는 실행 계획보기

DECLARE @INDEXNAME SYSNAME='인덱스이름';
SET @INDEXNAME=QUOTENAME(@INDEXNAME,'[');
WITH XMLNAMESPACES(DEFAULT 
        'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
        INDEXSEARCH
AS (
        SELECT QP.QUERY_PLAN,
               CP.USECOUNTS,
               IX.query('.') AS STMTSIMPLE
        FROM sys.dm_exec_cached_plans CP
        OUTER APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
        CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS P(IX)
        WHERE QUERY_PLAN.exist('//Object[@Index
                =sql:variable("@IndexName")]')=1
)
SELECT STMTSIMPLE.value('StmtSimple[1]/@StatementText',
                'VARCHAR(4000)') AS SQL_TEXT,
        OBJ.value('@Database','sysname') AS DATABASE_NAME,
        OBJ.value('@Schema','sysname') AS SCHEMA_NAME,
        OBJ.value('@Table','sysname') AS TABLE_NAME,
        OBJ.value('@Index','sysname') AS INDEX_NAME,
        IXS.query_plan
FROM INDEXSEARCH IXS
CROSS APPLY STMTSIMPLE.nodes('//Object') AS O(OBJ)
WHERE OBJ.exist('//Object[@Index=sql:variable("@IndexName")]')=1

Paralleism을 사용하는 실행 계획 보기

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES(DEFAULT 
        'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT COALESCE(DB_NAME(P.DBID),
        P.QUERY_PLAN.value('(//RelOp/OutputList/
                ColumnReference/@Database)[1]'
               ,'NVARCHAR(128)')) AS DATABASENAME,
        DB_NAME(P.DBID) +'.'
                +OBJECT_SCHEMA_NAME(P.OBJECTID,P.DBID)
                +'.'+OBJECT_NAME(P.OBJECTID,P.DBID) 
                AS OBJECTNAME,
        CP.OBJTYPE,
        P.QUERY_PLAN,
        CP.USECOUNTS,
        CP.PLAN_HANDLE,
        CAST('' 
                AS XML) 
                        AS SQLTEXT
FROM sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) P
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS Q
WHERE CP.CACHEOBJTYPE='Compiled Plan'
        AND P.QUERY_PLAN.exist('//RelOp[@Parallel="1"]')=1
ORDER BY DATABASENAME,USECOUNTS DESC

 

특정 인덱스에 대해 실행 계획에서 Index Scan , Index Seek 여부 확인하기

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
GO 
DECLARE @INDEXNAME SYSNAME;
DECLARE @OP SYSNAME;

SET @INDEXNAME ='PK_PolicyUserRole'
SET @OP='Index Scan';

WITH XMLNAMESPACES(DEFAULT 
        N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT CP.PLAN_HANDLE,
        DB_NAME(DBID)+'.'+OBJECT_SCHEMA_NAME(OBJECTID,DBID)+'.'
               +OBJECT_NAME(OBJECTID,DBID) AS DATABASE_OBJECT,
        QP.QUERY_PLAN,
        C1.value('@PhysicalOp','NVARCHAR(50)') 
                AS PHYSICAL_OPERATOR,
        C2.value('@Index','NVARCHAR(MAX)') AS INDEX_NAME
FROM sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_query_plan(CP.PLAN_HANDLE) QP
CROSS APPLY QUERY_PLAN.nodes('//RelOp') R(C1)
OUTER APPLY C1.nodes('IndexScan/Object') AS O(C2)
WHERE C2.value('@Index','NVARCHAR(MAX)') 
                = QUOTENAME(@INDEXNAME,'[')
        AND C1.exist('@PhysicalOp[.=sql:variable("@op")]')=1;

 

2. 프로시저의 캐쉬된 파라미터 정보 보여주기

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
WITH XMLNAMESPACES(DEFAULT 
        N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
        PLANPARAMETERS
AS (
        SELECT PH.PLAN_HANDLE,
               QP.QUERY_PLAN,
               QP.DBID,
               QP.OBJECTID
        FROM sys.dm_exec_cached_plans PH
        OUTER APPLY sys.dm_exec_query_plan(PH.PLAN_HANDLE) QP
        WHERE QP.QUERY_PLAN.exist('//ParameterList')=1
               AND OBJECT_NAME(QP.OBJECTID,QP.DBID)
                        ='프로시저 이름'
)
SELECT DB_NAME(PP.DBID) AS DATABASENAME,
        OBJECT_NAME(PP.OBJECTID,PP.DBID) AS OBJECTNAME,
        N2.value('(@Column)[1]','sysname') AS PARAMETERNAME,
        N2.value('(@ParameterCompiledValue)[1]','VARCHAR(MAX)') 
                AS PARAMETERVALUE
FROM PLANPARAMETERS PP
CROSS APPLY QUERY_PLAN.nodes('//ParameterList') AS Q1(N1)
CROSS APPLY N1.nodes('ColumnReference') AS Q2(N2)

※ 위에 예제들은 "SQL Server MVP Deep Dives" 서적에서 참조 하였으며,

"33. Investigating the plan cache" 장을 보면 원본을 보실 수가 있고,

좀더 자세한 내용들이 담겨져 있습니다.