DBA가 관리하고 있는 DBMS에 특정 저장프로시저가 재컴파일 되면서 뜻하지 않게 원하지 않는 플랜 즉, 잘못된 플랜이 만들어 지면서 서버의 리소스를 소비하는 현상을 경험한 적이 있는 DBA 분들이 있을꺼에요. (없다고요?? 그럼 경험해 보셔야 해요. ^^;;;)
아무튼 우리 회사에서도 특정 저장프로시저가 재컴파일 되면서 INDEX SEEK를 해야 함에도 INDEX SCAN으로 플랜이 만들어지고 이로 인해서 서버의 리소스가 사용률이 약간 상승하는 일이 비일비재 해요. 이를 해결하기 위하여 여러가지 방법이 있겠지만, 전 왠지 이런 방법은 없을까? 라는 생각을 해봤어요.
재컴파일 참고 URL : http://support.microsoft.com/kb/243586
특정 프로시저 즉, 관심대상이 되는 프로시저의 플랜을 저장해 두었다가 현재 캐쉬되어 실행되고 있는 플랜의 물리적 연산자와 저장해둔 플랜의 물리적 연산자가 틀려지게 되면 알 수 있는 방법이 없을까?
그래서 만들고 있지요.
- 특정 프로시저들의 플랜을 저장한다. (물론 테이블을 만들어서)
- 현재 캐쉬되고 실행되는 플랜의 물리적 연산자와 저장된 플랜의 물리적 연산자와 비교 한다.
- 동일하면 일치라는 메시지를 출력한다.
- 동일하지 않다면 현재 캐쉬되어 실행되고 있는 플랜의 물리적 연산자 중 SCAN 이라는 물리적 연산자가 있으면 몇 개가 있고 그 물리적 연산자로 출력되는 예상 행수가 몇 개인지 출력한다.
[위 스크린샷이 저장해 놓은 플랜과 현재 캐쉬되어 실행되고 있는 플랜과 동일할 경우 출력되는 메시지 입니다.]
[위 스크린샷이 저장해 놓은 플랜과 현재 캐쉬되어 실행되고 있는 플랜이 동일 하지 않다면 SCAN 정보를 출력하는 메시지 입니다.]
두번째 스크린샷의 연산자 해석 방법은 해당 플랜의 SCAN의 포함되어 있는 물리적 연산자는 Clustered Index Scan 과 Table Scan 이 있으며, Clustered Index Scan 은 해당 플랜에서 총 3번이 있으며 그 3번의 평균 예상행수는 26만건 이며, Table Scan은 해당 플랜에 총 1번 있으며 그 1번의 예상행수는 45건 이라는 의미입니다. 해당 로직을 사용해 보고 공유할 만한 스크립트가 만들어 지면 올리겠습니다. 근데 멋있지 않나요?? 저만 멋있는건가요 ㅜ.ㅜ;;
특정 프로시저가 재 컴파일이 되면서 실행계획에 문제가 발생하면 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만건이 있다고 출력을 해주고 있습니다. 그럼 이제 정상적인 플랜을 미리 저장해 두고 해당 스크립트로 현재 돌아가고 있는 플랜의 연산자를 추출하여 비교하면 어떤 연산자가 새로 생겼는지 기존에는 어떻게 돌고 있다가 재 컴파일 되면서 지금은 이렇게 돌고 있구나 라고 판단하실 수 있을 겁니다.
출처: https://ddoung2.tistory.com/359 [DDoung2]
'Database > SQL Server' 카테고리의 다른 글
| 악성 쿼리 찾아내기 - ReadTrace (0) | 2020.08.29 |
|---|---|
| Master Key (0) | 2020.08.29 |
| SQL Server2012 Checkpoint 제어 (0) | 2020.08.29 |
| sp_MSforeachdb (0) | 2020.08.29 |
| dbforge Activity Monitor Permission (0) | 2020.08.29 |