sys.dm_exec_query_plan(Transact-SQL)

적용 대상: SQL ServerAzure SQL Database Azure SQL Managed Instance

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

Showplan에 대한 XML 스키마가 게시되고 이 Microsoft 웹 사이트에서 사용할 수 있습니다. SQL Server가 설치된 디렉터리에서도 사용할 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

sys.dm_exec_query_plan(plan_handle)  

인수

plan_handle
실행된 일괄 처리에 대한 쿼리 실행 계획을 고유하게 식별하며 관련 계획이 계획 캐시에 있거나 현재 실행 중인 토큰입니다. plan_handle은 varbinary(64)입니다.

다음 동적 관리 개체에서 plan_handle을 얻을 수 있습니다.

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats(Transact-SQL)

• sys.dm_exec_requests(Transact-SQL)

• sys.dm_exec_procedure_stats(Transact-SQL)

• sys.dm_exec_trigger_stats(Transact-SQL)

반환된 테이블

열 이름	데이터 형식	설명
dbid	smallint	이 계획에 해당하는 Transact-SQL 문을 컴파일할 당시 유효했던 컨텍스트 데이터베이스의 ID입니다. 임시 및 준비된 SQL 문의 경우 문이 컴파일된 데이터베이스의 ID입니다. 열은 null을 허용합니다.
objectid	int	이 쿼리 계획에 대한 개체의 ID(예: 저장 프로시저 또는 사용자 정의 함수)입니다. 임시 및 준비된 일괄 처리의 경우 이 열은 null입니다. 열은 null을 허용합니다.
number	smallint	번호가 매겨진 저장 프로시저 정수입니다. 예를 들어 주문 애플리케이션에 대한 프로시저 그룹은 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 열에 Showplan 출력이 반환되지 않습니다.

• plan_handle을 사용하여 지정한 쿼리 계획이 계획 캐시에서 제거된 경우 반환된 테이블의 query_plan 열은 Null입니다. 예를 들어 계획 핸들이 캡처된 시간과 sys.dm_exec_query_plan 함께 사용된 시간 사이에 시간이 지연되는 경우 이 조건이 발생할 수 있습니다.

• 대량 작업 문이나 8KB를 넘는 문자열 리터럴이 포함된 문과 같은 일부 Transact-SQL 문은 캐시되지 않습니다. 캐시에 존재하지 않기 때문에 일괄 처리가 현재 실행되고 있지 않으면 이러한 문에 대한 XML 실행 계획을 sys.dm_exec_query_plan 사용하여 검색할 수 없습니다.

• Transact-SQL 일괄 처리 또는 저장 프로시저에 사용자 정의 함수에 대한 호출 또는 동적 SQL 호출(예: EXEC(문자열)을 사용하는 경우 사용자 정의 함수에 대한 컴파일된 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(9.x) 서비스 팩 2 이상 버전에서 query_plan 열은 NULL을 반환합니다.
sys.dm_exec_text_query_plan(Transact-SQL) 동적 관리 함수를 사용하여 쿼리 계획의 출력을 텍스트 형식으로 반환할 수 있습니다.

사용 권한

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

SQL Server 2022 이상에 대한 권한

서버에 대한 VIEW SERVER PERFORMANCE STATE 권한이 필요합니다.

예제

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

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

A. 실행 속도가 느린 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가 됨을 나타냅니다. 동적 관리 뷰와 함께 SPID를 sys.dm_exec_requests 사용하여 다음 쿼리를 사용하여 계획 핸들을 검색할 수 있습니다.

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

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

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

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

계획 캐시에 있는 모든 쿼리 계획의 스냅샷을 검색하려면 동적 관리 뷰를 쿼리하여 캐시에 있는 모든 쿼리 계획의 계획 핸들을 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 AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO  

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

서버가 통계를 수집한 현재 계획 캐시에 있는 모든 쿼리 계획의 스냅샷을 검색하려면 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 AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D. 평균 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  

참고 항목

동적 관리 뷰 및 함수(Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats(Transact-SQL)
sys.dm_exec_requests(Transact-SQL)
sp_who(Transact-SQL)
실행 계획 논리 및 물리 연산자 참조
sys.dm_exec_text_query_plan(Transact-SQL)