sys.dm_exec_text_query_plan(Transact-SQL)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Transact-SQL 일괄 처리 또는 일괄 처리 내 특정 문에 대한 텍스트 형식의 실행 계획을 반환합니다. 계획 핸들에서 지정한 쿼리 계획은 캐시되거나 현재 실행 중일 수 있습니다. 이 테이블 반환 함수는 sys.dm_exec_query_plan(Transact-SQL)과 비슷하지만 다음과 같은 차이점이 있습니다.

• 쿼리 계획의 출력은 텍스트 형식으로 반환됩니다.
• 쿼리 계획의 출력 크기는 제한되지 않습니다.
• 일괄 처리 내 개별 문을 지정할 수 있습니다.

적용 대상: SQL Server(SQL Server 2008(10.0.x) 이상), Azure SQL Database.

Transact-SQL 구문 규칙

구문

sys.dm_exec_text_query_plan   
(   
    plan_handle   
    , { statement_start_offset | 0 | DEFAULT }  
        , { statement_end_offset | -1 | DEFAULT }  
)  

인수

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)

statement_start_offset | 0 | 기본값
행이 일괄 처리 또는 지속형 개체의 텍스트 내에서 설명하는 쿼리의 시작 위치를 바이트 단위로 나타냅니다. statement_start_offset is int. 값이 0인 경우 일괄 처리의 시작을 나타냅니다. 기본값은 0입니다.

다음 동적 관리 개체에서 문 시작 오프셋을 가져올 수 있습니다.

• sys.dm_exec_query_stats

• sys.dm_exec_requests

statement_end_offset | -1 | 기본값
일괄 처리 또는 지속형 개체의 텍스트 내에서 행이 설명하는 쿼리의 끝 위치(바이트)를 나타냅니다.

statement_start_offset은 int입니다.

값 -1은 일괄 처리의 끝을 나타냅니다. 기본값은 -1입니다.

반환된 테이블

설명

다음 조건에서는 sys.dm_exec_text_query_plan 대해 반환된 테이블의 계획 열에 Showplan 출력이 반환되지 않습니다.

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

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

• EXEC(string)를 사용하는 경우와 같이 Transact-SQL 일괄 처리 또는 저장 프로시저에 사용자 정의 함수 호출이나 동적 SQL 호출이 포함된 경우 사용자 정의 함수에 대해 컴파일된 XML 실행 계획은 해당 일괄 처리 또는 저장 프로시저에 대해 sys.dm_exec_text_query_plan으로 반환되는 테이블에 포함되지 않습니다. 대신 사용자 정의 함수에 해당하는 plan_handle에 대해 sys.dm_exec_text_query_plan을 별도로 호출해야 합니다.

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

사용 권한

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

SQL Server 2022 이상에 대한 권한

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

예

A. 느리게 실행되는 Transact-SQL 쿼리 또는 일괄 처리에 대한 캐시된 쿼리 계획 검색

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

먼저 저장 프로시저를 사용하여 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나타냅니다. 다음 예제에서는 지정된 계획 핸들에 대한 쿼리 계획을 반환하고 기본값 0과 -1을 사용하여 쿼리 또는 일괄 처리의 모든 문을 반환합니다.

USE master;  
GO  
SELECT query_plan   
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);  
GO  

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

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

USE master;  
GO  
SELECT *   
FROM sys.dm_exec_cached_plans AS cp   
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);  
GO  

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

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

USE master;  
GO  
SELECT * FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);  
GO  

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

다음 예제에서는 상위 5개 쿼리에 대한 쿼리 계획 및 평균 CPU 시간을 반환합니다. sys.dm_exec_text_query_plan 함수는 쿼리 계획의 일괄 처리에 있는 모든 문을 반환하는 기본값 0과 -1을 지정합니다.

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_text_query_plan(qs.plan_handle, 0, -1)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

참고 항목

sys.dm_exec_query_plan(Transact-SQL)