sys.dm_exec_query_plan_stats(Transact-SQL)

아티클
07/12/2024

적용 대상: SQL Server 2019(15.x) Azure SQL 데이터베이스 Azure SQL Managed Instance

이전에 캐시된 쿼리 계획에 대해 마지막으로 알려진 실제 실행 계획에 해당하는 계획을 반환합니다.

구문

sys.dm_exec_query_plan_stats ( plan_handle )

인수

plan_handle

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

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

반환된 테이블

열 이름	데이터 형식	설명
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 값을 허용합니다.

설명

이 기능은 옵트인 기능입니다. 서버 수준에서 사용하도록 설정하려면 추적 플래그 2451을 사용합니다. 데이터베이스 수준에서 사용하도록 설정하려면 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)의 LAST_QUERY_PLAN_STATS 옵션을 사용합니다.

이 시스템 함수는 간단한 쿼리 실행 통계 프로파일링 인프라에서 작동합니다. 자세한 내용은 쿼리 프로파일링 인프라를 참조하세요.

sys.dm_exec_query_plan_stats의 실행 계획 출력에 포함된 정보는 다음과 같습니다.

캐시된 계획에 있는 모든 컴파일 시간 정보
연산자당 실제 행 수, 총 쿼리 CPU 시간 및 실행 시간, 유출 경고, 실제 DOP, 사용된 최대 메모리 및 부여된 메모리와 같은 런타임 정보

다음 조건에서는 실제 실행 계획에 해당하는 실행 계획 출력이 query_plan에 대해 반환된 테이블의 sys.dm_exec_query_plan_stats 열에 반환됩니다.

계획은 sys.dm_exec_cached_plans에서 찾을 수 있습니다.

및
실행 중인 쿼리는 복잡하거나 리소스를 사용합니다.

다음 조건에서는 기본 ¹ 실행 계획 출력이 query_plan에 대해 반환된 테이블의 sys.dm_exec_query_plan_stats 열에 반환됩니다.

계획은 sys.dm_exec_cached_plans에서 찾을 수 있습니다.

및
쿼리는 충분히 간단하며 일반적으로 OLTP 워크로드의 일부로 분류됩니다.

¹ 루트 노드 연산자(SELECT)만 포함된 실행 계획을 나타냅니다.

다음 조건에서는 sys.dm_exec_query_plan_stats에서 출력이 반환되지 않습니다.

plan_handle을 사용하여 지정한 쿼리 계획이 계획 캐시에서 제거되었습니다.

OR
쿼리 계획은 처음에 캐시할 수 없었습니다. 자세한 내용은 실행 계획 캐싱 및 재사용을 참조 하세요.

참고 항목

xml 데이터 형식에서 허용되는 중첩 수준 수의 제한은 sys.dm_exec_query_plan이 128개의 중첩 요소 수준을 충족하거나 초과하는 쿼리 계획을 반환할 수 없음을 의미합니다. 이전 버전의 SQL Server에서는 이 조건으로 인해 쿼리 계획이 반환되지 않고 오류 6335가 생성되었습니다. SQL Server 2005(9.x) 서비스 팩 2 이상 버전에서 query_plan 열은 NULL을 반환합니다.

사용 권한

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

SQL Server 2022 이상에 대한 사용 권한

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

예제

A. 마지막으로 알려진 실제 쿼리 실행 계획에서 캐시된 특정 계획 살펴보기

다음 예제에서는 sys.dm_exec_cached_plans를 쿼리하여 필요한 계획을 찾고 출력에서 plan_handle을 복사합니다.

SELECT * FROM sys.dm_exec_cached_plans;
GO

그런 다음 마지막으로 알려진 실제 쿼리 실행 계획을 가져오려면 시스템 함수 sys.dm_exec_query_plan_stats와 함께 복사된 plan_handle을 사용합니다.

SELECT * FROM sys.dm_exec_query_plan_stats(< copied plan_handle >);
GO

B. 마지막으로 알려진 실제 쿼리 실행 계획에서 캐시된 모든 계획 살펴보기

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO

C. 마지막으로 알려진 실제 쿼리 실행 계획에서 캐시된 특정 계획 및 쿼리 텍스트 살펴보기

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.text LIKE 'SELECT * FROM Person.Person%';
GO

D. 캐시된 이벤트에서 트리거 살펴보기

SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle)
WHERE objtype ='Trigger';
GO

다음을 통해 공유