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

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

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

 

  • 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 항목을 클릭하면 다음과 같이 그래픽 실행계획을 확인 할 수 있다.

 

 

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

 

참고자료


강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1458
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1227
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1087
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 930
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 911
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 940
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1854
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1250
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1222
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2649
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1682
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1702
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5583
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1336
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3653
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1745
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1239
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1254
» 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 862





XE Login