sys.dm_exec_query_plan(Transact-SQL)

아티클
06/18/2013

계획 핸들로 지정한 일괄 처리에 대한 XML 형식의 실행 계획을 반환합니다. 계획 핸들로 지정된 계획은 캐시되거나 현재 실행 중일 수 있습니다.

실행 계획의 XML 스키마는 게시되며 Microsoft 웹 사이트에서 사용할 수 있습니다. 또한 SQL Server이 설치된 디렉터리에서도 사용할 수 있습니다.

구문

sys.dm_exec_query_plan ( plan_handle )

인수

plan_handle
캐시되거나 현재 실행 중인 일괄 처리에 대한 쿼리 계획을 고유하게 식별합니다.

plan_handle은 varbinary(64)입니다. 다음 동적 관리 개체에서 plan_handle을 가져올 수 있습니다.

sys.dm_exec_cached_plans

sys.dm_exec_query_stats

sys.dm_exec_requests

반환된 테이블

열 이름	데이터 형식	설명
dbid	smallint	이 계획에 해당하는 Transact-SQL 문을 컴파일할 당시 유효했던 컨텍스트 데이터베이스의 ID입니다. 임시 및 준비된 SQL 문의 경우 문이 컴파일된 데이터베이스의 ID입니다. 열이 Null 값을 허용합니다.
objectid	int	이 쿼리 계획에 대한 저장 프로시저나 사용자 정의 함수와 같은 개체의 ID입니다. 임시 및 준비된 일괄 처리의 경우 이 열은 Null입니다. 열이 Null 값을 허용합니다.
number	smallint	번호가 매겨진 저장 프로시저 정수입니다. 예를 들어 orders 응용 프로그램에 대한 프로시저 그룹에 orderproc;1, orderproc;2 등의 이름을 지정할 수 있습니다. 임시 및 준비된 일괄 처리의 경우 이 열은 Null입니다. 열이 Null 값을 허용합니다.
encrypted	bit	해당 저장 프로시저가 암호화되었는지 여부를 나타냅니다. 0 = 암호화되지 않음 1 = 암호화됨 열은 Null을 허용하지 않습니다.
query_plan	xml	plan_handle로 지정한 쿼리 실행 계획의 컴파일 시간 실행 계획 표현을 포함합니다. 실행 계획은 XML 형식입니다. 임시 Transact-SQL 문, 저장 프로시저 호출, 사용자 정의 함수 호출 등이 포함된 각 일괄 처리에 대해 계획 하나가 생성됩니다. 열이 Null 값을 허용합니다.

주의

다음과 같은 경우 sys.dm_exec_query_plan에 대해 반환된 테이블의 query_plan 열에 실행 계획 출력이 반환되지 않습니다.

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

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

xml 데이터 형식에서 허용된 중첩 수준 수의 제한으로 인해 sys.dm_exec_query_plan은 중첩 요소의 128개 수준을 충족하거나 초과하는 쿼리 계획을 반환할 수 없습니다. 이전 버전의 SQL Server에서는 이 상태로 인해 쿼리 계획을 반환하지 못했으므로 오류 6335가 발생합니다. SQL Server 2005 서비스 팩 2 이상 버전에서는 query_plan 열이 NULL을 반환합니다. sys.dm_exec_text_query_plan(Transact-SQL) 동적 관리 함수를 사용하여 쿼리 계획의 출력을 텍스트 형식으로 반환할 수 있습니다.

사용 권한

sys.dm_exec_query_plan을 실행하려면 sysadmin 고정 서버 역할의 멤버이거나 해당 서버에서 VIEW SERVER STATE 권한이 있어야 합니다.

예

다음 예에서는 sys.dm_exec_query_plan 동적 관리 뷰를 사용하는 방법을 보여 줍니다.

XML 실행 계획을 보려면 SQL Server Management Studio의 쿼리 편집기에서 다음 쿼리를 실행하고 sys.dm_exec_query_plan에 의해 반환된 테이블의 query_plan 열에서 ShowPlanXML을 클릭합니다. Management Studio 보고서 요약 창에 XML 실행 계획이 표시됩니다. XML 실행 계획을 파일로 저장하려면 query_plan 열에서 ShowPlanXML을 마우스 오른쪽 단추로 클릭하고 다른 이름으로 결과 저장을 클릭한 다음 <file_name>.sqlplan 형식(예: MyXMLShowplan.sqlplan)으로 파일 이름을 지정합니다.

1.실행 속도가 느린 Transact-SQL 쿼리 또는 일괄 처리에 대한 캐시된 쿼리 계획 검색

임시 일괄 처리, 저장 프로시저, 사용자 정의 함수 등 다양한 유형의 Transact-SQL 일괄 처리에 대한 쿼리 계획은 계획 캐시라는 메모리 영역에서 캐시됩니다. 캐시된 쿼리 계획 각각은 계획 핸들이라는 고유 식별자로 식별됩니다. sys.dm_exec_query_plan 동적 관리 뷰에 이 계획 핸들을 지정하여 특정 Transact-SQL 쿼리 또는 일괄 처리에 대한 실행 계획을 검색할 수 있습니다.

SQL Server에 대한 특정 연결에서 Transact-SQL 쿼리 또는 일괄 처리가 오랫동안 실행되는 경우 이 쿼리나 일괄 처리에 대한 실행 계획을 검색하여 지연 원인을 알아낼 수 있습니다. 다음 예에서는 실행 속도가 느린 쿼리나 일괄 처리에 대한 XML 실행 계획을 검색하는 방법을 보여 줍니다.

[!참고]

이 예를 실행하려면 session_id 및 plan_handle 값을 사용자의 서버에 해당하는 값으로 바꿉니다.

먼저 sp_who 저장 프로시저를 사용하여 쿼리 또는 일괄 처리를 실행 중인 프로세스의 SPID(서버 프로세스 ID)를 검색합니다.

USE master;
GO
exec sp_who;
GO

sp_who에 의해 반환되는 결과 집합은 SPID가 54임을 나타냅니다. sys.dm_exec_requests 동적 관리 뷰에 이 SPID를 사용하여 다음 쿼리를 통해 계획 핸들을 검색할 수 있습니다.

USE master;
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = 54;
GO

sys.dm_exec_requests에 의해 반환되는 테이블은 실행 속도가 느린 쿼리나 일괄 처리의 계획 핸들이 0x06000100A27E7C1FA821B10600임을 나타냅니다. sys.dm_exec_query_plan을 사용하여 이 계획 핸들을 plan_handle 인수로 지정하면 다음과 같이 XML 형식의 실행 계획을 검색할 수 있습니다. 실행 속도가 느린 쿼리나 일괄 처리에 대한 XML 형식의 실행 계획은 sys.dm_exec_query_plan에 의해 반환되는 테이블의 query_plan 열에 포함됩니다.

USE master;
GO
SELECT * FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);
GO

2.계획 캐시에서 모든 쿼리 계획 검색

계획 캐시에 있는 모든 쿼리 계획의 스냅숏을 검색하려면 sys.dm_exec_cached_plans 동적 관리 뷰를 쿼리하여 캐시에 있는 모든 쿼리 계획의 계획 핸들을 검색합니다. 계획 핸들은 sys.dm_exec_cached_plans의 plan_handle 열에 저장됩니다. 그런 다음 CROSS APPLY 연산자를 사용하여 다음과 같이 계획 핸들을 sys.dm_exec_query_plan으로 전달합니다. 계획 캐시에 있는 각 계획의 XML 실행 계획 출력은 현재 반환된 테이블의 query_plan 열에 있습니다.

USE master;
GO
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO

3.서버가 계획 캐시에서 쿼리 통계를 수집한 모든 쿼리 계획 검색

서버가 통계를 수집한 현재 계획 캐시에 있는 모든 쿼리 계획의 스냅숏을 검색하려면 sys.dm_exec_query_stats 동적 관리 뷰를 쿼리하여 캐시에서 이 계획의 계획 핸들을 검색합니다. 계획 핸들은 sys.dm_exec_query_stats의 plan_handle 열에 저장됩니다. 그런 다음 CROSS APPLY 연산자를 사용하여 다음과 같이 계획 핸들을 sys.dm_exec_query_plan으로 전달합니다. 서버가 통계를 수집한 현재 계획 캐시에 있는 각 계획의 XML 실행 계획 출력은 반환된 테이블의 query_plan 열에 있습니다.

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);
GO

4.평균 CPU 시간별 상위 5개 쿼리에 대한 정보 검색

다음 예에서는 상위 5개 쿼리에 대한 계획과 평균 CPU 시간을 반환합니다.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
GO