세션에 따른 캐시된 쿼리 플랜 설정 확인

세션에 따른 캐시된 쿼리 플랜 설정 확인

 

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

 

사용자가 SQL 문장을 실행하면 User Process가 server Process에게 해당 SQL문장을 전달 한다. 전달받은 서버 프로세스는 해당 SQL 문을 처리하는데 다음과 같은 순서로 진행 된다.

 

 

Parse는 SQL 문장에 쓰인 키워드나 컬럼명 등을 분석해서 Parse Tree를 생성하게 된다. Parse Tree를 만드는 과정에서 문법 검사. 의미검사 등을 하게 된다. 간혹 스펠링이 틀린 경우 Parse Tree 단계에서 오류가 발생하고 철자 오류는 없지만 테이블 등이 없는 경우 semantic Check에서 오류가 발생 한다.

해당 문법에 오류는 없는지, 해당 테이블이 존재하는지를 알기 위해 데이터 딕셔너리를 사용하게 되고 자주 사용되는 데이터딕셔너리를 캐시에 두어 성능을 높이는 역할 을 하는 곳이 Shared Pool안에 있는 Dictionary Cache 이다.

 

해당 SQL 문에 오류가 없다면 SQL문장에 대한 실행계획을 생성해야 하는데 옵티마이저에게 실행계획 생성을 시키기 전에 Shared Pool의 Library Cache에 요청 받은SQL 문장과 같은 실행 계획이 있는지 검사한다. 이 과정을 Soft 공유 또는 Soft Parse 라 한다.

이때 같은 쿼리문 이라도 사용자가 다르면 다른 쿼리로 인식 한다. 옵티마이저는 실행계획을 세울 때 데이터 딕셔너리 정보를 보고 판단하는데 이때 옵티마이저가 새로운 실행계획을 세우는 단계를 Hard Parse라고 한다.

 

그 다음 Bind 및 Execute 과정을 통하여 사용자가 요청한 정보가 담겨 있는 블록을 디스크에서 찾아서 database buffer cache에 복사해서 Fetch 과정. 즉 사용자가 요청한 원하는 데이터만 골라내어 반환한다.

 

위에 설명 하였듯이 캐시된 실행계획에서 캐시된 실행계획 쿼리와 사용된 원본 쿼리가 다른 경우 재사용 되지 않는다. 아래 DMV를 통하여 세션에 따른 실행계획을 확인 할 수 있다.

• Sys.dm_exec_cached_plans : 캐시에 있는 모든 실행계획 목록.
• Sys.dm_exec_query_plan : Planhandle에 대한 실행계획을 그래픽, XML 형식으로 반환
• Sys.dm_exec_plan_attribute : 실행계획에 대한 세션 수준 설정 제공.

select b.query_plan, c.attribute, c.value from sys.dm_exec_cached_plans as a cross apply sys.dm_exec_query_plan (a.plan_handle) as b cross apply sys.dm_exec_plan_attributes (a.plan_handle) as c where a.plan_handle = (0x050004008EC1217F40C19882000000000000000000000000)

 

 

 

위의 스크립트에서는 planhadle을 이용하여 조회할 수 있지만 특정 사용자에 대해서는 한번에 확인 할 수 없다. DMV는 확장이 매우 편한 구조로써 다음과 같은 방법으로 사용자에 대한 쿼리 플랜의 상태를 확인 할 수 있다.

select     d.query_plan, e.attribute, e.value from sys.dm_exec_cached_plans as a     inner join sys.dm_exec_requests as b on a.plan_handle = b.plan_handle     inner join sys.dm_exec_sessions as c on b.session_id = c.session_id     cross apply sys.dm_exec_query_plan (a.plan_handle) as d     cross apply sys.dm_exec_plan_attributes (a.plan_handle) as e where c.login_name = 'NXK\jevida'

 

 

결과 집합에서 query_plan 항목을 클릭하면 다음과 같이 그래픽 실행계획을 확인 할 수 있다.

 

 

캐시된 계획과 세션에 설정된 계획이 일치하는지 확인하고 재사용을 사용할 수 있도록 세션상태를 구성하여 성능 향상을 높일 수 있도록 하자.

 

참고자료

• http://technet.microsoft.com/ko-kr/library/ms187404.aspx
• http://msdn.microsoft.com/ko-kr/library/ms189747.aspx
• http://msdn.microsoft.com/ko-kr/library/ms189472.aspx

 

2013-01-17 / 강성욱 / http://sqlmvp.kr