아티클
04/01/2012

sys.dm_exec_plan_attributes(Transact-SQL)

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

[!참고]

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

구문

sys.dm_exec_plan_attributes ( plan_handle )

인수

plan_handle
실행된 일괄 처리에 대한 쿼리 계획을 고유하게 식별하며 해당 계획은 계획 캐시에 있습니다. plan_handle은 varbinary(64)입니다. 계획 핸들은 sys.dm_exec_cached_plans 동적 관리 뷰에서 얻을 수 있습니다.

반환된 테이블

열 이름	데이터 형식	설명
attribute	varchar(128)	이 계획과 연결된 특성의 이름입니다. 다음 중 하나일 수 있습니다. 특성데이터 형식설명 set_options int 계획을 컴파일할 때 사용하는 옵션 값을 나타냅니다. objectid int 캐시에서 개체를 찾는 데 사용되는 기본 키 중 하나입니다. 이것은 sys.objects에 저장된 데이터베이스 개체(프로시저, 뷰, 트리거 등)의 개체 ID입니다. 계획 유형이 "임시" 또는 "준비됨"일 경우 일괄 처리 텍스트의 내부 해시입니다. dbid int 계획이 참조하는 엔터티를 포함하는 데이터베이스의 ID입니다. 임시 또는 준비된 계획의 경우 일괄 처리가 실행된 데이터베이스 ID입니다. dbid_execute int Resource 데이터베이스에 저장된 시스템 개체의 경우 캐시된 계획이 실행된 데이터베이스 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)를 참조하십시오. status int 캐시 조회 키의 일부인 내부 상태 비트입니다. required_cursor_options int 커서 유형과 같이 사용자가 지정한 커서 옵션입니다. acceptable_cursor_options int SQL Server가 문의 실행을 지원하도록 암시적으로 변환될 수 있는 커서 옵션입니다. 예를 들어 사용자는 동적 커서를 지정할 수 있지만 쿼리 최적화 프로그램을 통해 이 커서 유형을 정적 커서로 변환할 수 있습니다. 자세한 내용은 암시적 커서 변환 사용을 참조하십시오. 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 핸들입니다. merge_action_type smallint MERGE 문의 결과로 사용되는 트리거 실행 계획의 유형입니다. 0은 비 트리거 계획, MERGE 문의 결과로 실행되지 않는 트리거 계획 또는 DELETE 동작만 지정하는 MERGE 문의 결과로 실행되는 트리거 계획을 나타냅니다. 1은 MERGE 문의 결과로 실행되는 INSERT 트리거 계획을 나타냅니다. 2는 MERGE 문의 결과로 실행되는 UPDATE 트리거 계획을 나타냅니다. 3은 해당 INSERT 또는 UPDATE 동작을 포함하는 MERGE 문의 결과로 실행되는 DELETE 트리거 계획을 나타냅니다. 동작을 연계하여 실행되는 중첩 트리거의 경우 이 값은 연계를 유발하는 MERGE 문의 동작입니다.
value	sql_variant	이 계획과 연결된 특성의 값입니다.
is_cache_key	bit	특성이 계획에 대한 캐시 조회 키의 일부로 사용되는지 여부를 나타냅니다.

사용 권한

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

주의

SET 옵션

동일하게 컴파일된 계획의 복사본에서 set_options 열의 값만 서로 다를 수 있습니다. 이는 동일한 쿼리에 대해 서로 다른 연결이 서로 다른 SET 옵션 집합을 사용하고 있음을 나타냅니다. 캐시에 있는 계획의 여러 복사본으로 인해 추가 컴파일 작업이 발생하고 계획 재사용 횟수가 줄어들며 계획 캐시 인플레이션이 발생할 수 있으므로 일반적으로 서로 다른 옵션 집합은 사용하지 않는 것이 좋습니다. 자세한 내용은 쿼리 튜닝 권장 사항을 참조하십시오.

SET 옵션 평가

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

옵션	값
ANSI_PADDING	1
Parallel Plan	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
UPON 계획을 컴파일할 때 데이터베이스 옵션 PARAMETERIZATION이 FORCED로 설정됨을 나타냅니다.	131072

커서

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

커서 옵션 평가

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

예

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

다음 예에서는 지정된 계획에 대한 모든 계획 특성을 반환합니다. 지정된 계획에 대한 계획 핸들을 얻기 위해 먼저 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

2. 컴파일된 계획의 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