Compartilhar via

  • Artigo

sys.dm_exec_cached_plans (Transact-SQL)

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.

ObservaçãoObservaçã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 este objeto de cache foi usado desde o começ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:

ValorDescrição
ProcProcedimento armazenado
PreparedInstrução preparada
AdhocConsulta ad hoc1
ReplProcProcedimento de filtro de replicação
TriggerGatilho
ViewExibição
DefaultPadrão
UsrTabTabela de usuário
SysTabTabela do sistema
CheckRestrição CHECK
RuleRegra

plan_handle

varbinary(64)

Identificador do plano na memória. Este 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 a 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