다음을 통해 공유

sys.dm_exec_text_query_plan(Transact-SQL)

  • 아티클

적용 대상: SQL Server Azure SQL 데이터베이스 Azure 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을 얻을 수 있습니다.

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

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

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

statement_start_offset은 int입니다.

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

반환된 테이블

열 이름 데이터 형식 설명
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 nvarchar(max) plan_handle로 지정한 쿼리 실행 계획의 컴파일 시간 실행 계획 표현을 포함합니다. 실행 계획은 텍스트 형식입니다. 임시 Transact-SQL 문, 저장 프로시저 호출, 사용자 정의 함수 호출 등이 포함된 각 일괄 처리에 대해 계획 하나가 생성됩니다.

열이 Null 값을 허용합니다.

설명

다음 조건에서는 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 쿼리 또는 일괄 처리가 오랫동안 실행되는 경우 이 쿼리나 일괄 처리에 대한 실행 계획을 검색하여 지연 원인을 알아낼 수 있습니다. 다음 예에서는 실행 속도가 느린 쿼리나 일괄 처리에 대한 실행 계획을 검색하는 방법을 보여 줍니다.

참고 항목

이 예를 실행하려면 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나타냅니다. 다음 예제에서는 지정된 계획 핸들에 대한 쿼리 계획을 반환하고 기본값 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)