sys.dm_exec_query_plan(Transact-SQL)

적용 대상:SQL ServerAzure SQL DatabaseAzure 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)