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

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'

 

 

 

 

[참고자료]

• https://blogs.msdn.microsoft.com/psssql/2016/07/13/why-am-i-getting-null-values-for-query_plan-from-sys-dm_exec_query_plan/
• https://msdn.microsoft.com/ko-kr/library/ms189747.aspx

 

 

2016-10-11 / 강성욱 / http://sqlmvp.kr

 

 

MS SQL, SQL Server, Query Plan, sys.dm_exec_query_plan, 실행계획, 쿼리 튜닝, 쿼리 플랜, DMV