sys.dm_exec_cached_plans (Transact-SQL)

Artigo
06/18/2013

Retorna uma linha para cada plano de consulta armazenado em cache pelo SQL Server a fim de acelerar a execução da consulta. É 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.

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.

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 que o objeto de cache foi examinado. Não incrementado quando consultas parametrizadas localizam um plano no cache. Pode ser incrementado várias vezes durante o us do plano de execução.

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:

Valor	Descrição
Proc	Procedimento armazenado
Prepared	Instrução preparada
Adhoc	Consulta ad hoc1
ReplProc	Procedimento de filtro de replicação
Gatilho	Gatilho
Exibir	Exibir
Padrão	Padrão
UsrTab	Tabela de usuário
SysTab	Tabela do sistema
Verificar	Restrição CHECK
Regra	Regra

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