sys.dm_exec_plan_attributes(Transact-SQL)

적용 대상:SQL Server

계획 핸들에서 지정한 계획의 계획 특성당 한 행을 반환합니다. 이 테이블 반환 함수를 사용하여 계획의 현재 동시 실행 수 또는 캐시 키 값과 같은 특정 계획에 대한 정보를 가져올 수 있습니다.

참고 항목

이 함수를 통해 반환된 일부 정보는 sys.syscacheobjects 이전 버전과의 호환성 보기에 매핑 됩니다 .

구문

sys.dm_exec_plan_attributes ( plan_handle )  

인수

plan_handle
실행되고 계획이 계획 캐시에 있는 일괄 처리에 대한 쿼리 계획을 고유하게 식별합니다. plan_handle은 varbinary(64)입니다. 계획 핸들은 sys.dm_exec_cached_plans 동적 관리 뷰에서 가져올 수 있습니다.

반환된 테이블

열 이름	데이터 형식	설명
특성	varchar(128)	이 계획과 연결된 특성의 이름입니다. 이 표 바로 아래의 표에는 가능한 특성, 해당 데이터 형식 및 설명이 나열되어 있습니다.
value	sql_variant	이 계획과 연결된 특성의 값입니다.
is_cache_key	bit	특성이 계획에 대한 캐시 조회 키의 일부로 사용되는지 여부를 나타냅니다.

위의 표 에서 특성 에는 다음 값이 있을 수 있습니다.

Attribute	데이터 형식	설명
set_options	int	계획을 컴파일할 때 사용하는 옵션 값을 나타냅니다.
objectid	int	캐시에서 개체를 찾는 데 사용되는 기본 키 중 하나입니다. 데이터베이스 개체(프로시저, 뷰, 트리거 등)에 대한 sys.objects에 저장된 개체 ID입니다. "Adhoc" 또는 "Prepared" 형식의 계획의 경우 일괄 처리 텍스트의 내부 해시입니다.
dbid	int	계획이 참조하는 엔터티를 포함하는 데이터베이스의 ID입니다. 임시 또는 준비된 계획의 경우 일괄 처리가 실행되는 데이터베이스 ID입니다.
dbid_execute	int	리소스 데이터베이스에 저장된 시스템 개체의 경우 캐시된 계획이 실행되는 데이터베이스 ID입니다. 다른 모든 경우는 0입니다.
user_id	int	-2 값은 제출된 일괄 처리가 암시적 이름 확인에 의존하지 않으며 다른 사용자 간에 공유할 수 있음을 나타냅니다. 이는 선호되는 방법입니다. 다른 값은 데이터베이스에서 쿼리를 제출하는 사용자의 사용자 ID를 나타냅니다.
language_id	smallint	캐시 개체를 만든 연결 언어의 ID입니다. 자세한 내용은 sys.syslanguages(Transact-SQL)를 참조하세요.
date_format	smallint	캐시 개체를 만든 연결의 날짜 형식입니다. 자세한 내용은 SET DATEFORMAT(Transact-SQL)를 참조하세요.
date_first	tinyint	날짜의 첫 번째 값입니다. 자세한 내용은 SET DATEFIRST(Transact-SQL)를 참조하세요.
compat_level	tinyint	쿼리 계획이 컴파일된 컨텍스트의 데이터베이스에 설정된 호환성 수준을 나타냅니다. 반환되는 호환성 수준은 임시 문에 대한 현재 데이터베이스 컨텍스트의 호환성 수준이며 쿼리 힌트 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 영향을 받지 않습니다. 저장 프로시저 또는 함수에 포함된 문의 경우 저장 프로시저 또는 함수가 만들어지는 데이터베이스의 호환성 수준에 해당합니다.
status	int	캐시 조회 키의 일부인 내부 상태 비트입니다.
required_cursor_options	int	커서 유형과 같이 사용자가 지정한 커서 옵션입니다.
acceptable_cursor_options	int	문 실행을 지원하기 위해 SQL Server가 암시적으로 변환할 수 있는 커서 옵션입니다. 예를 들어 사용자는 동적 커서를 지정할 수 있지만 쿼리 최적화 프로그램은 이 커서 형식을 정적 커서로 변환할 수 있습니다.
merge_action_type	smallint	MERGE 문의 결과로 사용되는 트리거 실행 계획의 형식입니다. 0은 트리거가 아닌 계획, MERGE 문의 결과로 실행되지 않는 트리거 계획 또는 DELETE 동작만 지정하는 MERGE 문의 결과로 실행되는 트리거 계획을 나타냅니다. 1은 MERGE 문의 결과로 실행되는 INSERT 트리거 계획을 나타냅니다. 2는 MERGE 문의 결과로 실행되는 UPDATE 트리거 계획을 나타냅니다. 3은 해당 INSERT 또는 UPDATE 작업을 포함하는 MERGE 문의 결과로 실행되는 DELETE 트리거 계획을 나타냅니다. 연계 작업을 통해 실행되는 중첩된 트리거의 경우 이 값은 CASCADE를 발생시킨 MERGE 문의 동작입니다.
is_복제본(replica)tion_specific	int	이 계획이 컴파일된 세션이 문서화되지 않은 연결 속성을 사용하여 SQL Server 인스턴스에 연결된 세션임을 나타내며, 이를 통해 서버가 복제본(replica)tion 구성 요소에서 만든 세션으로 식별할 수 있으므로 서버의 특정 기능적 측면의 동작이 이러한 복제본(replica)tion 구성 요소에 따라 변경됩니다.
optional_spid	smallint	다시 컴파일 수를 줄이기 위해 연결 session_id(spid)가 캐시 키의 일부가 됩니다. 이렇게 하면 비동기적으로 바인딩된 임시 테이블과 관련된 계획을 단일 세션의 다시 컴파일할 수 없습니다.
optional_clr_trigger_dbid	int	CLR DML 트리거의 경우에만 채워집니다. 엔터티를 포함하는 데이터베이스의 ID입니다. 다른 개체 형식의 경우 0을 반환합니다.
optional_clr_trigger_objid	int	CLR DML 트리거의 경우에만 채워집니다. sys.objects에 저장된 개체 ID입니다. 다른 개체 형식의 경우 0을 반환합니다.
parent_plan_handle	varbinary(64)	항상 NULL입니다.
is_azure_user_plan	tinyint	사용자가 시작한 세션에서 Azure SQL Database에서 실행된 쿼리의 경우 1입니다. 최종 사용자가 시작하지 않고 Azure 인프라 내에서 실행되는 애플리케이션이 원격 분석을 수집하거나 관리 작업을 실행하는 다른 목적으로 쿼리를 실행하는 세션에서 실행된 쿼리의 경우 0입니다. 고객은 is_azure_user_plan = 0인 쿼리에서 사용하는 리소스에 대해 요금이 부과되지 않습니다. Azure SQL Database 만 해당합니다.
inuse_exec_context	int	쿼리 계획을 사용하는 현재 실행 중인 일괄 처리 수입니다.
free_exec_context	int	현재 사용되지 않는 쿼리 계획의 캐시된 실행 컨텍스트 수입니다.
hits_exec_context	int	계획 캐시에서 실행 컨텍스트를 가져와 다시 사용한 횟수로, SQL 문을 다시 컴파일하는 오버헤드를 절약합니다. 이 값은 지금까지의 모든 일괄 처리 실행에 대한 집계입니다.
misses_exec_context	int	계획 캐시에서 실행 컨텍스트를 찾지 못하고 일괄 처리 실행에 대한 새 실행 컨텍스트를 생성한 횟수입니다.
removed_exec_context	int	캐시된 계획의 메모리 압력으로 인해 제거된 실행 컨텍스트의 수입니다.
inuse_cursors	int	캐시된 계획을 사용하는 하나 이상의 커서가 포함된 현재 실행 중인 일괄 처리 수입니다.
free_cursors	int	캐시된 계획에 대한 유휴 또는 무료 커서 수입니다.
hits_cursors	int	캐시된 계획에서 비활성 커서를 가져와 다시 사용한 횟수입니다. 이 값은 지금까지의 모든 일괄 처리 실행에 대한 집계입니다.
misses_cursors	int	캐시에서 비활성 커서를 찾지 못한 횟수입니다.
removed_cursors	int	캐시된 계획의 메모리 압력으로 인해 제거된 커서의 수입니다.
sql_handle	varbinary(64)	일괄 처리에 대한 SQL 핸들입니다.

사용 권한

SQL Server에서 VIEW SERVER STATE 권한이 필요합니다.

Azure SQL Database Basic, S0 및 S1 서비스 목표 및 탄력적 풀 의 데이터베이스에는 서버 관리자 계정 또는 Microsoft Entra 관리자 계정이 필요합니다. 다른 모든 SQL Database 서비스 목표에서는 데이터베이스에 VIEW DATABASE STATE 권한이 필요합니다.

SQL Server 2022 이상에 대한 권한

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

설명

옵션 설정

동일한 컴파일된 계획의 복사본은 set_options 열의 값에 의해서만 다를 수 있습니다. 이는 서로 다른 연결이 동일한 쿼리에 대해 서로 다른 SET 옵션 집합을 사용하고 있음을 나타냅니다. 캐시에 있는 계획의 여러 복사본으로 인해 추가 컴파일, 적은 계획 재사용 및 계획 캐시 인플레이션이 발생할 수 있으므로 일반적으로 다른 옵션 집합을 사용하는 것은 바람직하지 않습니다.

설정 옵션 평가

set_options 반환된 값을 계획이 컴파일된 옵션으로 변환하려면 가능한 가장 큰 값부터 시작하여 0에 도달할 때까지 set_options 값에서 값을 뺍니다. 뺀 각 값은 쿼리 계획에 사용된 옵션에 해당합니다. 예를 들어 set_options 값이 251인 경우 계획이 컴파일된 옵션은 ANSI_NULL_DFLT_ON(128), QUOTED_IDENTIFIER(64), ANSI_NULLS(32), ANSI_WARNINGS(16), CONCAT_NULL_YIELDS_NULL(8), 병렬 계획(2) 및 ANSI_PADDING(1)입니다.

옵션	값
ANSI_PADDING	1
ParallelPlan 계획 병렬 처리 옵션이 변경되었음을 나타냅니다.	2
FORCEPLAN	4
CONCAT_NULL_YIELDS_NULL	8
ANSI_WARNINGS	16
ANSI_NULLS	32
QUOTED_IDENTIFIER	64
ANSI_NULL_DFLT_ON	128
ANSI_NULL_DFLT_OFF	256
NoBrowseTable 계획이 FOR BROWSE 작업을 구현하는 데 작업 테이블을 사용하지 않음을 나타냅니다.	512
TriggerOneRow 계획에 AFTER 트리거 델타 테이블에 대한 단일 행 최적화가 포함됨을 나타냅니다.	1024
ResyncQuery 내부 시스템 저장 프로시저에서 쿼리를 제출했음을 나타냅니다.	2048
ARITH_ABORT	4096
NUMERIC_ROUNDABORT	8192
DATEFIRST	16384
DATEFORMAT	32768
LanguageID	65536
시 계획을 컴파일할 때 데이터베이스 옵션 PARAMETERIZATION이 FORCED로 설정되었음을 나타냅니다.	131072
ROWCOUNT	적용 대상: SQL Server 2012(11.x) 이상 262144

커서

커서를 저장하는 데 사용되는 메모리를 커서의 동시 사용자가 다시 사용할 수 있도록 비활성 커서는 컴파일된 계획에 캐시됩니다. 예를 들어 일괄 처리에서 커서를 할당 취소하지 않고 선언하고 사용한다고 가정합니다. 동일한 일괄 처리를 실행하는 두 명의 사용자가 있는 경우 두 개의 활성 커서가 있습니다. 커서의 할당이 취소되면(잠재적으로 다른 일괄 처리에서) 커서를 저장하는 데 사용되는 메모리가 캐시되고 해제되지 않습니다. 이 비활성 커서 목록은 컴파일된 계획에 유지됩니다. 다음에 사용자가 일괄 처리를 실행할 때 캐시된 커서 메모리가 다시 사용되며 활성 커서로 적절하게 초기화됩니다.

커서 옵션 평가

required_cursor_options 반환된 값을 변환하고 계획을 컴파일한 옵션으로 acceptable_cursor_options 0에 도달할 때까지 가능한 가장 큰 값부터 시작하여 열 값에서 값을 뺍니다. 빼는 각 값은 쿼리 계획에서 사용된 커서 옵션에 해당합니다.

옵션	값
없음	0
INSENSITIVE	1
SCROLL	2
READ ONLY	4
FOR UPDATE	8
LOCAL	16
GLOBAL	32
FORWARD_ONLY	64
KEYSET	128
DYNAMIC	256
SCROLL_LOCKS	512
OPTIMISTIC	1024
STATIC	2048
FAST_FORWARD	4096
IN PLACE	8192
FOR select_statement	16384

예제

A. 특정 계획에 대한 특성 반환

다음 예제에서는 지정된 계획에 대한 모든 계획 특성을 반환합니다. sys.dm_exec_cached_plans 동적 관리 뷰는 지정된 계획에 대한 계획 핸들을 가져오기 위해 먼저 쿼리됩니다. 두 번째 쿼리에서 첫 번째 쿼리의 계획 핸들 값으로 바꿉 <plan_handle> 다.

SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype   
FROM sys.dm_exec_cached_plans;  
GO  
SELECT attribute, [value], is_cache_key  
FROM sys.dm_exec_plan_attributes(<plan_handle>);  
GO  

B. 컴파일된 계획에 대한 SET 옵션 및 캐시된 계획에 대한 SQL 핸들 반환

다음 예제에서는 각 계획이 컴파일된 옵션을 나타내는 값을 반환합니다. 또한 캐시된 모든 계획에 대한 SQL 핸들이 반환됩니다.

SELECT plan_handle, pvt.set_options, pvt.sql_handle  
FROM (  
    SELECT plan_handle, epa.attribute, epa.value   
    FROM sys.dm_exec_cached_plans   
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa  
    WHERE cacheobjtype = 'Compiled Plan') AS ecpa   
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;  
GO  

참고 항목

동적 관리 뷰 및 함수(Transact-SQL)
실행 관련 동적 관리 뷰 및 함수(Transact-SQL)
sys.dm_exec_cached_plans(Transact-SQL)
sys.databases(Transact-SQL)
sys.objects(Transact-SQL)