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





profile

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

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.