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 동적 관리 뷰에서 가져올 수 있습니다.
반환된 테이블
| 열 이름 | 데이터 형식 | 설명 |
|---|---|---|
| attribute | 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_replication_specific | int | 이 계획이 컴파일된 세션은 서버가 복제 구성 요소에서 만든 세션으로 세션을 식별할 수 있도록 문서화되지 않은 연결 속성을 사용하여 SQL Server 인스턴스에 연결된 세션이므로 서버의 특정 기능적 측면의 동작이 이러한 복제 구성 요소에 따라 변경됨을 나타냅니다. |
| 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에 도달할 때까지 가능한 가장 큰 값부터 시작하여 열 값에서 값을 뺍니다. 빼는 각 값은 쿼리 계획에서 사용된 커서 옵션에 해당합니다.
| 옵션 | 값 |
|---|---|
| None | 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)