다음을 통해 공유


sys.dm_exec_cached_plans (Transact-SQL)

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)

더 빠른 쿼리 실행을 위해 SQL Server에서 캐시하는 각 쿼리 계획에 대한 행을 반환합니다. 이 동적 관리 뷰를 사용하여 캐시된 쿼리 계획, 캐시된 쿼리 텍스트, 캐시된 계획에서 가져온 메모리 양 및 캐시된 계획의 재사용 횟수를 찾을 수 있습니다.

Azure SQL Database에서 동적 관리 뷰는 데이터베이스 포함에 영향을 주거나 사용자가 액세스할 수 있는 다른 데이터베이스에 대한 정보를 노출하는 정보를 노출할 수 없습니다. 이 정보를 노출하지 않으려면 연결된 테넌트에 속하지 않는 데이터가 포함된 모든 행이 필터링됩니다. 또한 열의 값은 memory_object_address pool_id 필터링되고 열 값은 NULL로 설정됩니다.

참고 항목

Azure Synapse Analytics 또는 PDW(Analytics Platform System)에서 이를 호출하려면 이름을 sys.dm_pdw_nodes_exec_cached_plans사용합니다. 이 구문은 Azure Synapse Analytics의 서버리스 SQL 풀에서 지원되지 않습니다.

열 이름 데이터 형식 설명
bucketid int 항목이 캐시되는 해시 버킷의 ID입니다. 값은 캐시 형식에 대한 해시 테이블 크기까지의 범위를 나타냅니다.

SQL 계획 및 개체 계획 캐시의 경우 해시 테이블 크기는 32비트 시스템에서는 최대 10007, 64비트 시스템에서는 최대 40009가 될 수 있습니다. 바운드 트리 캐시의 경우 해시 테이블 크기는 32비트 시스템에서는 최대 1009, 64비트 시스템에서는 최대 4001개까지 가능합니다. 확장 저장 프로시저 캐시의 경우 해시 테이블 크기는 32비트 및 64비트 시스템에서 최대 127개일 수 있습니다.
refcounts int 이 캐시 개체를 참조하는 캐시 개체의 수입니다. 항목이 캐시에 있도록 하려면 Refcount가 1 이상이어야 합니다.
usecounts int 캐시 개체를 조회한 횟수입니다. 매개 변수가 있는 쿼리가 캐시에서 계획을 찾을 때 증가하지 않습니다. 실행 계획을 사용할 때 여러 번 증분할 수 있습니다.
size_in_bytes int 캐시 개체가 사용한 바이트 수입니다.
memory_object_address varbinary(8) 캐시된 항목의 메모리 주소입니다. 이 값은 sys.dm_os_memory_objects 사용하여 캐시된 계획의 메모리 분석을 가져오고 sys.dm_os_memory_cache_entries_entries 사용하여 항목을 캐싱하는 비용을 가져올 수 있습니다.
cacheobjtype nvarchar(34) 캐시에 있는 개체의 형식입니다. 값은 다음 중 하나입니다.

컴파일된 계획

컴파일된 계획 스텁

구문 분석 트리

확장된 절차

CLR Compiled Func

CLR Compiled Proc
objtype nvarchar(16) 개체의 유형입니다. 다음은 가능한 값 및 해당 설명입니다.

Proc: 저장 프로시저
준비: 준비된 문
임시: 임시 쿼리입니다. 원격 프로시저 호출 대신 osql 또는 sqlcmd를 사용하여 언어 이벤트로 제출된 Transact-SQL을 참조합니다.
ReplProc: Replication-filter-procedure
트리거: 트리거
보기: 보기
기본값: 기본값
UsrTab: 사용자 테이블
SysTab: 시스템 테이블
Check: CHECK 제약 조건
규칙: 규칙
plan_handle varbinary(64) 메모리 내 계획의 식별자입니다. 이 식별자는 일시적이며 계획이 캐시에 남아 있는 동안에만 일정하게 유지됩니다. 이 값은 다음과 같은 동적 관리 함수와 함께 사용할 수 있습니다.

sys.dm_exec_sql_text

sys.dm_exec_query_plan

sys.dm_exec_plan_attributes
pool_id int 이 계획 메모리 사용량이 고려되는 리소스 풀의 ID입니다.
pdw_node_id int 적용 대상: Azure Synapse Analytics, Analytics Platform System(PDW)

이 배포가 있는 노드의 식별자입니다.

1

사용 권한

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

SQL Database Basic, S0S1 서비스 목표 및 탄력적 풀의 데이터베이스에는 서버 관리자 계정, Microsoft Entra 관리자 계정 또는 서버 역할##MS_ServerStateReader## 멤버 자격이 필요합니다. 다른 모든 SQL Database 서비스 목표에 대해서는 데이터베이스에 대한 VIEW DATABASE STATE 권한 또는 ##MS_ServerStateReader## 서버 역할의 멤버 자격이 필요합니다.

SQL Server 2022 이상에 대한 권한

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

예제

A. 다시 사용하는 캐시된 항목의 일괄 처리 텍스트 반환

다음 예제에서는 두 번 이상 사용된 모든 캐시된 항목의 SQL 텍스트를 반환합니다.

SELECT usecounts, cacheobjtype, objtype, text   
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_sql_text(plan_handle)   
WHERE usecounts > 1   
ORDER BY usecounts DESC;  
GO  

B. 캐시된 모든 트리거에 대한 쿼리 계획 반환

다음 예제에서는 캐시된 모든 트리거의 쿼리 계획을 반환합니다.

SELECT plan_handle, query_plan, objtype   
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_query_plan(plan_handle)   
WHERE objtype ='Trigger';  
GO  

C. 계획이 컴파일된 SET 옵션 반환

다음 예제에서는 계획이 컴파일된 SET 옵션을 반환합니다. sql_handle 계획도 반환됩니다. PIVOT 연산자는 행이 아닌 열로 특성 및 sql_handle 특성을 출력 set_options 하는 데 사용됩니다. 반환된 set_options값에 대한 자세한 내용은 sys.dm_exec_plan_attributes(Transact-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  

D. 캐시된 모든 컴파일된 계획의 메모리 분석 반환

다음 예제에서는 캐시의 컴파일된 모든 계획에서 사용하는 메모리의 분석을 반환합니다.

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,   
    omo.memory_object_address, type, page_size_in_bytes   
FROM sys.dm_exec_cached_plans AS ecp   
JOIN sys.dm_os_memory_objects AS omo   
    ON ecp.memory_object_address = omo.memory_object_address   
    OR ecp.memory_object_address = omo.parent_address  
WHERE cacheobjtype = 'Compiled Plan';  
GO  

참고 항목

동적 관리 뷰 및 함수(Transact-SQL)
실행 관련 동적 관리 뷰 및 함수(Transact-SQL)
sys.dm_exec_query_plan(Transact-SQL)
sys.dm_exec_plan_attributes(Transact-SQL)
sys.dm_exec_sql_text(Transact-SQL)
sys.dm_os_memory_objects(Transact-SQL)
sys.dm_os_memory_cache_entries(Transact-SQL)
FROM(Transact-SQL)