Artigo
04/01/2012

sys.dm_exec_cached_plans (Transact-SQL)

Retorna uma linha para cada plano de consulta que é armazenada pelo SQL Server para execução de consulta mais rápida. É possível usar esta exibição de gerenciamento dinâmico para localizar planos de consulta em cache, texto de consulta em cache, a quantidade de memória usada pelos planos em cache e o número de reutilizações dos planos em cache.

Observação
Para planos de consulta, a exibição de gerenciamento dinâmico sys.dm_exec_cached_plans é mapeada para a tabela do sistema syscacheobjects no SQL Server 2000.

Nome da coluna	Tipo de dados	Descrição
bucketid	int	ID do segmento de hash em que a entrada é armazenada em cache. O valor indica um intervalo de 0 ao tamanho de tabela de hash para o tipo de cache. Para os caches de planos SQL e planos de objeto, o tamanho da tabela de hash pode ser de até 10007 em sistemas de 32 bits e até 40009 em sistemas de 64 bits. Para os caches de árvores associadas, o tamanho da tabela de hash pode ser de até 1009 em sistemas de 32 bits e até 4001 em sistemas de 64 bits. Para os caches de procedimentos armazenados estendidos, o tamanho da tabela de hash pode ser de até 127 em sistemas de 32 e 64 bits. Para obter mais informações sobre tipos de cache e tabelas de hash, consulte sys.dm_os_memory_cache_hash_tables (Transact-SQL).
refcounts	int	Número de objetos de cache que fazem referência a este objeto de cache. Refcounts deve ser pelo menos 1 para que uma entrada fique no cache.
usecounts	int	Número de vezes em que o objeto em cache foi procurado. Não incrementado quando consultas parametrizadas encontram um plano no cache. Pode ser incrementado várias vezes quando o plano de execução é usado.
size_in_bytes	int	Número de bytes consumidos pelo objeto de cache.
memory_object_address	varbinary(8)	Endereço de memória da entrada em cache. Esse valor pode ser usado com sys.dm_os_memory_objects para obter a análise de memória do plano em cache e com entradas sys.dm_os_memory_cache_entries para obter o custo do armazenamento em cache da entrada.
cacheobjtype	nvarchar(34)	Tipo de objeto no cache. O valor pode ser um dos seguintes: Compiled Plan Compiled Plan Stub Parse Tree Extended Proc CLR Compiled Func CLR Compiled Proc
objtype	nvarchar(16)	Tipo de objeto. O valor pode ser um dos seguintes: ValorDescrição ProcProcedimento armazenado PreparedInstrução preparada AdhocConsulta ad hoc1 ReplProcProcedimento de filtro de replicação TriggerGatilho ViewExibição PadrãoPadrão UsrTabTabela de usuário SysTabTabela do sistema VerificarRestrição CHECK RegraRegra
plan_handle	varbinary(64)	Identificador do plano na memória. Esse identificador é transitório e permanece constante somente enquanto o plano permanecer no cache. Este valor pode ser usado com as seguintes funções de gerenciamento dinâmico: sys.dm_exec_sql_text sys.dm_exec_query_plan sys.dm_exec_plan_attributes
pool_id	int	ID do pool de recursos no qual o uso de memória do plano é contabilizado.

1 Refere-se ao Transact-SQL enviado como eventos de linguagem com o uso de osql ou sqlcmd em vez de chamadas de procedimento remotas.

Permissões

Requer a permissão VIEW SERVER STATE no servidor.

Exemplos

A. Retornando o texto de lote de entradas em cache que são reutilizadas

O exemplo seguinte retorna o texto SQL de todas as entradas em cache que foram usadas mais de uma vez.

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. Retornando planos de consulta para todos os gatilhos em cache

O exemplo seguinte retorna os planos de consulta de todos os gatilhos em cache.

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. Retornando as opções SET com que o plano foi compilado

O exemplo seguinte retorna as opções SET com que o plano foi compilado. O sql_handle do plano também é retornado. O operador PIVOT é usado para produzir os atributos set_options e sql_handle como colunas em vez de linhas. Para obter mais informações sobre o valor retornado em set_options, consulte 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. Retornando a análise de memória de todos os planos compilados em cache

O exemplo seguinte retorna uma análise da memória usada por todos os planos compilados no cache.

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject, 
    omo.memory_object_address, pages_allocated_count, 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