저장된 Plan Cache 확인 및 활용

저장된 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)

• http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/

 

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는 자신이 운용하는 서버의 캐시를 주기적으로 확인하여 변경된 사항은 없는지 정상적으로 플랜을 사용하고 있는지 등을 파악하여 시스템 최적화를 이끌어 낼 수 있을 듯 하다.

 

[참고자료]

• SQL Server Plan Cache: The Junk Drawer for Your Queries

  http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries

 

• Plan cache, adhoc workloads and clearing the single-use plan cache bloat

  http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/