데이터베이스 개발자 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
» 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 866
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 713
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 2984
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1291
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1063
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1565
1705 BCP 사용 jevida(강성욱) 2016.09.13 3380
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1153
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 2474
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 7078
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1565
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1609
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1796
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1575
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 702
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1812
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 10929
1694 SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2061
1693 SQL Server Net Libraries jevida(강성욱) 2016.09.13 1109
1692 SQL Server 별칭 설정 및 사용 jevida(강성욱) 2016.09.13 2647





XE Login