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

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

Sys.dm_exec_query_plan 에서 query_plan 컬럼의 NULL 값 반환

 

  • Version : SQL Server 2008, 2008 R2, 2012, 2014, 2016

 

SQL Server에서 쿼리 계획을 확인하기 위한 방법에는 DMV를 사용하는 방법이 있다. Sys.dm_exec_query_plan DMV는 XML 형태의 실행 계획을 반환한다. 플랜 핸들로 지정된 계획은 캐시되거나 현재 실행 중일 수 있다. 이 DMV는 아래와 같은 정보를 반환한다.

컬럼명

데이터 형식

설명

dbid

smallint

컴파일 당시 데이터베이스 ID. (NULL허용)

Objectid

Int

저장 프로시저나 사용자 정의 함수와 같은 개체 ID. ad-hoc 및 prepared 일괄처리의 경우 NULL 반환. (NULL 허용)

Number

Smallint

번호가 매겨진 저장프로시저 정수. Ad-hoc 및 prepared 일괄처리의 경우 NULL값 반환. (NULL허용)

Encrypted

Bit

해당 저장 프로시저가 암호화 되었는지 여부를 나타냄.

0 = 암호화 되지 않음

1= 암호화됨

(NULL 비허용)

Query_plan

xml

Plan_handle로 지정한 쿼리 실행 계획의 컴파일 시간 실행 계획을 포함. 실행계획은 XML로 표시. (NULL 허용)

 

아래와 같이query_plan의 결과 값이 특정 상황에서는 NULL이 반환되는 경우가 있다.

  • Plan_handle을 이용하여 지정한 쿼리 계획이 캐시에서 삭제된 경우 NULL 값을 반환한다. 예를 들어 플랜 핸들을 캡처한 시간과 sys.dm_exec_query_plan에 사용한 시간 사이에 지연이 있을 경우 발생할 수 있다.
  • 대량 작업문이나 8KB를 넘는 리터럴이 포함된 문자과 같은 일부 T-SQL문은 캐시 되지 않는다. 이러한 XML 실행 계획은 캐시에 없기 때문에 일괄 처리가 현재 실행되고 있지 않으면 sys.dm_exec_query_plan을 사용하여 검색 할 수 없다.
  • EXEC(string)을 사용하는 경우와 같이 T-SQL 일괄 처리 또는 저장프로시저에 사용자 정의 함수 호출이나 동적 SQL 호출이 포함된 경우 사용자 정의 함수에 대해 컴파일된 XML 실행 계획은 해당 일괄 처리 또는 저장 프로시저에 대해 sys.dm_exec_query_plan으로 반환되는 테이블에 포함되지 않는다. 대신 사용자 정의 함수에 해당하는 플랜 핸들에 대해 sys.dm_exec_query_plan을 별도로 호출 해야 한다.

 

Ad-hoc쿼리에서 간단한 매개변수화 또는 강제 매개변수화를 사용하는 경우 query_plan 열에는 텍스트만 포함되고 실제 쿼리 계획은 포함되지 않는다. 쿼리 계획을 반환하려면 매개 변수가 있는 준비된 쿼리의 계획 핸들에 대한 sys.dm_exec_query_plan을 호출해야 한다. Sys.syscacheobjects 뷰의 SQL 열 또는 sys.dm_exec_sql_text 동적 관리 뷰의 텍스트 열을 참조하여 쿼리가 매개 변수화 되었는지 확인할 수 있다.

 

XML 데이터 형식에서 허용된 중첩 수준 수의 제한으로 인해 sys.dm_exec_query_plan은 중첩 요소의 128개 수준을 충족하거나 초과하는 쿼리 계획을 반환할 수 없다. SQL Server 2005 SP2 이하 버전에서는 6335 오류가 반환되며 그 이상 버전에서는 NULL 값이 반환 된다.

 

아래 실습을 통해서 query_plan 값이 NULL 이 반환되는 경우를 살펴본다. 아래 스크립트는 리컴파일을 실행하여 일부 실행계획을 반환하지 못하는 경우이다.

use tempdb

go

 

create table t1 (c1 int)

go

 

create table t2 (c1 int)

go

 

create procedure p_test @option int

as

 

if @option >= 2

select * from t1 option (recompile)

 

if @option >=1

select * from t2 option (recompile)

go

 

exec p_test 1

go

 

 

SELECT

    plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, s3.query_plan AS text_query_plan

FROM sys.dm_exec_cached_plans s1

    CROSS APPLY sys.dm_exec_sql_text(plan_handle) s2

    CROSS APPLY sys.dm_exec_query_plan(plan_handle) s3

WHERE object_name ( s2.objectid, s2.dbid) = 'p_test'

 

 

 

 

[참고자료]

 



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

No. Subject Author Date Views
2070 SQL Server 2016 Multiple Log Writer Workers jevida(강성욱) 2017.01.11 3807
2069 SQL Server 2016 Larger Data File Writes jevida(강성욱) 2017.01.11 3188
2068 SQL Server 2016 향상된 업데이트 스케줄링 알고리즘 jevida(강성욱) 2017.01.11 3651
2067 SQL Server 2016 Automatic Soft NUMA jevida(강성욱) 2017.01.11 3103
2066 SQL Server 2016 Tempdb 환경 설정 jevida(강성욱) 2017.01.11 4394
2065 SQL Server 2016 향상된 즉시 파일 초기화 jevida(강성욱) 2017.01.11 2756
2064 SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴 jevida(강성욱) 2017.01.11 2635
2063 SQL Server 2016 Tempdb 성능 향상 jevida(강성욱) 2017.01.11 4725
2062 SQL Server 2016 DBCC CHECK 작업 성능 향상 jevida(강성욱) 2017.01.11 2376
2061 Microsoft Azure - SQL Server가 포함된 가상 컴퓨터 생성하기 jevida(강성욱) 2017.01.11 1840
2060 데이터베이스에서 사용자 삭제 오류 jevida(강성욱) 2017.01.11 1305
» Sys.dm_exec_query_plan 에서 query_plan 컬럼의 NULL 값 반환 jevida(강성욱) 2017.01.11 1875
2058 SSMS에서 유효하지 않은 소유자로 데이터베이스 정보가 보이지 않는 증상 jevida(강성욱) 2017.01.11 1853
2057 SQLCMD 유틸리티 사용하기 jevida(강성욱) 2017.01.11 7907
2056 In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상 jevida(강성욱) 2017.01.11 4547
2055 SQL Server Job Agent는 몇 개까지 실행이 가능할까? jevida(강성욱) 2017.01.11 1757
2054 확장이벤트 사용시 주의사항 jevida(강성욱) 2017.01.11 1631
2053 Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기 jevida(강성욱) 2017.01.11 2981
2052 Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화 jevida(강성욱) 2017.01.11 1571
2051 시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기 jevida(강성욱) 2017.01.11 2332





XE Login