sys.dm_exec_text_query_plan(Transact-SQL)
Transact-SQL 일괄 처리 또는 일괄 처리 내 특정 문에 대한 텍스트 형식의 실행 계획을 반환합니다. 계획 핸들로 지정된 쿼리 계획은 캐시되거나 현재 실행 중일 수 있습니다. 이 테이블 반환 함수는 sys.dm_exec_query_plan(Transact-SQL)과 비슷하지만 다음과 같은 차이점이 있습니다.
쿼리 계획의 출력은 텍스트 형식으로 반환됩니다.
쿼리 계획의 출력 크기는 제한되지 않습니다.
일괄 처리 내 개별 문을 지정할 수 있습니다.
.gif "항목 링크 아이콘")
구문
sys.dm_exec_text_query_plan
(
plan_handle
, { statement_start_offset | 0 | DEFAULT }
, { statement_end_offset | -1 | DEFAULT }
)
인수
plan_handle
캐시되거나 현재 실행 중인 일괄 처리에 대한 쿼리 계획을 고유하게 식별합니다. plan_handle은 varbinary(64)입니다.다음 동적 관리 개체에서 계획 핸들을 얻을 수 있습니다.
statement_start_offset | 0 | DEFAULT
일괄 처리 또는 지속형 개체의 텍스트 내에서 행이 설명하는 쿼리의 시작 위치(바이트)를 나타냅니다. statement_start_offset은 int입니다. 값이 0인 경우 일괄 처리의 시작을 나타냅니다. 기본값은 0입니다.다음 동적 관리 개체에서 문 시작 오프셋을 얻을 수 있습니다.
statement_end_offset | -1 | DEFAULT
일괄 처리 또는 지속형 개체의 텍스트 내에서 행이 설명하는 쿼리의 끝 위치(바이트)를 나타냅니다.statement_start_offset은 int입니다.
값이 -1인 경우 일괄 처리의 끝을 나타냅니다. 기본값은 -1입니다.
반환된 테이블
열 이름 |
데이터 형식 |
설명 |
|---|---|---|
dbid |
smallint |
이 계획에 해당하는 Transact-SQL 문을 컴파일할 당시 유효했던 컨텍스트 데이터베이스의 ID입니다. 임시 및 준비된 일괄 처리의 경우 이 열은 Null입니다. 열이 Null 값을 허용합니다. |
objectid |
int |
이 쿼리 계획에 대한 저장 프로시저나 사용자 정의 함수와 같은 개체의 ID입니다. 임시 및 준비된 일괄 처리의 경우 이 열은 Null입니다. 열이 Null 값을 허용합니다. |
number |
smallint |
번호가 매겨진 저장 프로시저 정수입니다. 예를 들어 orders 응용 프로그램에 대한 프로시저 그룹에 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에 대해 반환된 테이블의 plan 열에 실행 계획 출력이 반환되지 않습니다.
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 권한이 있어야 합니다.
예
1. 실행 속도가 느린 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임을 나타냅니다. sys.dm_exec_requests 동적 관리 뷰에 이 SPID를 사용하여 다음 쿼리를 통해 계획 핸들을 검색할 수 있습니다.
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
2. 계획 캐시에서 모든 쿼리 계획 검색
계획 캐시에 있는 모든 쿼리 계획의 스냅숏을 검색하려면 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
3. 서버가 계획 캐시에서 쿼리 통계를 수집한 모든 쿼리 계획 검색
서버가 통계를 수집한 현재 계획 캐시에 있는 모든 쿼리 계획의 스냅숏을 검색하려면 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
4. 평균 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