데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

저장된 Plan Cache 확인 및 활용

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서 요청된 쿼리는 컴파일 과정을 거쳐 실행 계획을 생성한다. 이때 요청된 쿼리에 따라 수 많은 계획이 생성될 수도 있고 기존의 계획이 재사용 될 수도 있다. 이번 포스트에서는 저장된 캐시 계획에 대한 활용방안을 살펴 보자.

 

비슷한 방식으로 실행 계획을 저장하는 SQL Server의 내부 메모리 영역(플랜캐시 또는 프로시저 캐시라고도 한다)이 있다. SQL Server는 기존 계획이 내부에 존재하는지 먼저 확인한다. 그리고 기존의 계획을 발견하게 되면 요청된 쿼리에 대해 새로운 계획에 대한 컴파일 시간을 할애 하지 않아도 된다. 이렇게 기존의 계획을 재사용하면 일반적인 성능을 높일 수 있다.

 

SQL Server에 저장되어 있는 플랜캐시를 확인하여 자주 사용하는 플랜 및 사용하지 않는 플랜을 분석 할 수가 있다. 플랜캐시는 시간이 지남에 따라 실행되는 모든 쿼리에 대한 자세한 내용을 저장한다. 우리는 주기적으로 캐시의 내용을 검사 할 수 있다. 또한 성능 문제를 조사하기 위해 플랜캐시를 확인 할 수 있다.

 

[캐시 내부의 단일 실행 계획을 찾는 방법]

한 번만 실행되는 쿼리문에 대해 저장된 쿼리 계획을 갖는 것은 큰 문제가 되지 않는다. 하지만 많은 단일 실행 계획이 있을 때 문제가 발생 한다. 이는 플랜캐시의 사용량 증가와 함께 CPU 리소스 사용의증가로 전체적인 성능저하가 발생한다.

다음 스크립트는 플랜캐시에서 한번 사용된 계획을 나타낸다.

SELECT

    text, cp.objtype, cp.size_in_bytes

FROM sys.dm_exec_cached_plans AS cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

WHERE cp.cacheobjtype = N'Compiled Plan'

    AND cp.objtype IN(N'Adhoc', N'Prepared')

    AND cp.usecounts = 1

ORDER BY cp.size_in_bytes DESC

OPTION (RECOMPILE);

 

 

SQL Server2008은 플랜 캐시 사이즈의 증가를 최소화 하기 위해 임시워크로드(ad-hoc workload)에 대한 구성 옵션 최적화를 도입했다. 이 옵션은 첫 번째 실행에 대해서는 전체 계획을 저장하지 않는다. 단지 계획 스텁은 플랜캐시 내부의 적은 공간을 차지하도록 생성된다. 계획이 두 번 사용하면 다음에는 완전히 컴파일된 계획을 저장한다. 임시워크로드 최적화는 기본적으로 활성화 되어 있지 않다.

 

위의 스크립트는 한 번만 사용되는 명령문을 결정하는데 매우 큰 도움이 된다. 그러나 이러한 단일 사용계획이 정말 문제가 있는 경우는 어떻게 알 수 있을까? 다음 스크립트를 사용하여 단일 사용 계획에 사용되는 메모리 양을 측정하고 전체 플랜캐시의 크기를 비교할 수 있다.

SELECT

    objtype AS [CacheType]

    , count_big(*) AS [Total Plans]

    , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

    , avg(usecounts) AS [Avg Use Count]

    , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]

    , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs - USE Count 1] DESC

 

 

전체 플랜캐시에 비해 단일 사용 계획에 대한 메모리 소비의 권장된 사항은 없지만 단일 실행 계획이 전체 플랜캐시의 50%이상 사용할 경우 임시워크로드(ad-hoc workload) 옵션에 대한 최적화 사용을 검토해 보는 것이 좋다.

 

 

[한 번 사용된 캐시 계획 삭제]

한 번 사용된 플랜 캐시를 삭제하고 싶을 때에는 다음 스크립트를 사용한다. (Kimberly Tripp)

 

DECLARE @MB decimal(19,3) , @Count bigint, @StrMB nvarchar(20)

 

 

SELECT

     @MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024

    ,@Count = sum(CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN 1 ELSE 0 END)

,@StrMB = convert(nvarchar(20), @MB)

FROM sys.dm_exec_cached_plans

 

 

IF @MB > 10

BEGIN

DBCC FREESYSTEMCACHE('SQL Plans')

RAISERROR ('%s MB was allocated to single-use plan cache. Single-use plans have been cleared.', 10, 1, @StrMB)

END

ELSE

BEGIN

RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB)

END

go

 

 

[최적화가 필요한 플랜캐시 찾기]

다음 스크립트는 인덱스가 누락된 현재의 모든 계획을 찾을 수 있는 쿼리이다.

SELECT

    dec.usecounts, dec.refcounts, dec.objtype

,dec.cacheobjtype, des.dbid, des.text

,deq.query_plan

FROM sys.dm_exec_cached_plans AS dec

CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des

CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq

WHERE

deq.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0

ORDER BY dec.usecounts DESC

 

 

다음 스크립트는 암시적 변환 경고를 이용한 플랜캐시를 찾는 방법이다. 암시적 변환은 데이터베이스에 정의된 데이터 유형과 쿼리에 사용된 유형의 데이터 타입 불일치를 나타낸다.

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

SELECT

    cp.query_hash,cp.query_plan_hash,

    ConvertIssue= operators.value('@ConvertIssue', 'nvarchar(250)'),

    Expression= operators.value('@Expression', 'nvarchar(250)'), qp.query_plan

FROM sys.dm_exec_query_stats cp

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    CROSS APPLY query_plan.nodes('//Warnings/PlanAffectingConvert') rel(operators)

 

 

다음 스크립트는 캐시에 있는 각 계획의 내부 계획에 대한 결과를 반환 한다.

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

SELECT

    cp.query_hash,cp.query_plan_hash,

    PhysicalOperator= operators.value('@PhysicalOp','nvarchar(50)'),

    LogicalOp= operators.value('@LogicalOp','nvarchar(50)'),

    AvgRowSize= operators.value('@AvgRowSize','nvarchar(50)'),

    EstimateCPU= operators.value('@EstimateCPU','nvarchar(50)'),

    EstimateIO= operators.value('@EstimateIO','nvarchar(50)'),

    EstimateRebinds= operators.value('@EstimateRebinds','nvarchar(50)'),

    EstimateRewinds= operators.value('@EstimateRewinds','nvarchar(50)'),

    EstimateRows= operators.value('@EstimateRows','nvarchar(50)'),

    Parallel= operators.value('@Parallel','nvarchar(50)'),

    NodeId= operators.value('@NodeId','nvarchar(50)'),

    EstimatedTotalSubtreeCost= operators.value('@EstimatedTotalSubtreeCost','nvarchar(50)')

FROM sys.dm_exec_query_stats cp

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    CROSS APPLY query_plan.nodes('//RelOp') rel(operators)

 

 

 

[비슷한 계획을 찾는 방법]

쿼리의 해시 함수 값은 비슷하지만 문자 값이 다를 경우 다른 계획이 생성 된다.

 

다음 예시를 통해 살펴보자. Where 조건만 다르게 하여 2개의 쿼리를 실행 한다.

SELECT P.FirstName, P.LastName

FROM Person.Person AS P

WHERE P.FirstName = 'Amanda'

GO

 

SELECT P.FirstName, P.LastName

FROM Person.Person AS P

WHERE P.FirstName = 'Logan'

GO

 

 

생성된 계획을 살펴보자.

SELECT

    st.text, qs.query_hash

FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

 

 

쿼리 해시는 동일하나 서로 다른 문자 값인 것을 확인 할 수 있다.

 

다음 스크립트를 이용하면 동일한 계획을 확인 할 수 있다.

SELECT COUNT(*) AS [Count], query_stats.query_hash,

query_stats.statement_text AS [Text]

FROM

(SELECT QS.*,

SUBSTRING(ST.text,(QS.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(ST.text)

ELSE QS.statement_end_offset END

- QS.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats

GROUP BY query_stats.query_hash, query_stats.statement_text

ORDER BY 1 DESC

 

 

위 결과를 바탕으로 유사한 쿼리 해시 값을 많이 가진 경우 대신에 사용되는 하나의 매개 변수가 있는 문을 작성하는 것이 좋다. 이것은 많은 계획을 하나의 계획으로 저장할 수 있다.

 

하나의 매개 변수가 있는 쿼리를 작성하는 코드를 변경할 수 없는 경우에는 실행계획 재사용의 극대화를 위해 플랜 가이드를 사용하는 것이 좋다.

 

 

SQL Server 캐싱된 계획은 SQL Server에서 실행되는 쿼리에 대한 전반적인 정보를 가지고 있다. 시스템의 속도가 저하될 경우 캐시된 데이터를 확인 할 수 있다. DBA는 자신이 운용하는 서버의 캐시를 주기적으로 확인하여 변경된 사항은 없는지 정상적으로 플랜을 사용하고 있는지 등을 파악하여 시스템 최적화를 이끌어 낼 수 있을 듯 하다.

 

[참고자료]

 

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1544
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1721
» 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4501
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1715
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1537
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1528
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1536
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7703
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2106
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1241
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1770
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1767
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1370
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1469
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1854
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1643
1834 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1388
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1157
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2472
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421





XE Login