Condividi tramite

  • Articolo

sys.dm_exec_cached_plans (Transact-SQL)

Restituisce una riga per ogni piano di query memorizzato nella cache da SQL Server per velocizzare l'esecuzione di query. È possibile utilizzare questa DMV per trovare i piani di query memorizzati nella cache, il testo delle query memorizzato nella cache, la quantità di memoria utilizzata dai piani memorizzati nella cache e il numero di riutilizzi dei piani nella cache.

Nota

Per i piani di query, la DMV sys.dm_exec_cached_plans corrisponde alla tabella di sistema syscacheobjects in SQL Server 2000.

Nome colonna

Tipo di dati

Descrizione

bucketid

int

ID dell'hash bucket in cui la voce viene memorizzata nella cache. Il valore indica un intervallo compreso tra 0 e le dimensioni della tabella hash per il tipo di cache.

Per le cache di tipo Piani SQL e Piani per gli oggetti, le dimensioni massime della tabella hash sono 10007 nei sistemi a 32 bit e 40009 nei sistemi a 64 bit. Per la cache di tipo Alberi associati, le dimensioni massime della tabella hash sono 1009 nei sistemi a 32 bit e 4001 nei sistemi a 64 bit. Per la cache di tipo Stored procedure estese, le dimensioni massime della tabella cache sono 127 nei sistemi a 32 e a 64 bit. Per ulteriori informazioni sui tipi di cache e le tabelle hash, vedere sys.dm_os_memory_cache_hash_tables (Transact-SQL).

refcounts

int

Numero di oggetti della cache che fanno riferimento a questo oggetto della cache. Refcounts deve essere almeno 1 perché la cache contenga una voce.

usecounts

int

Numero di volte in cui l'oggetto della cache è stato cercato. Non viene incrementato quanto le query con parametri trovano un piano nella cache. Può essere incrementato più volte quando si utilizza showplan.

size_in_bytes

int

Numero di byte utilizzati dall'oggetto della cache.

memory_object_address

varbinary(8)

Indirizzo di memoria della voce memorizzata nella cache. È possibile utilizzare questo valore con sys.dm_os_memory_objects per recuperare la suddivisione di memoria del piano memorizzato nella cache e con sys.dm_os_memory_cache_entries per ottenere il costo della memorizzazione della voce nella cache.

cacheobjtype

nvarchar(34)

Tipo di oggetto nella cache. I possibili valori sono i seguenti:

  • Compiled Plan

  • Compiled Plan Stub

  • Parse Tree

  • Extended Proc

  • CLR Compiled Func

  • CLR Compiled Proc

objtype

nvarchar(16)

Tipo di oggetto. I possibili valori sono i seguenti:

ValoreDescrizione
ProcStored procedure
PreparedIstruzione preparata
AdhocQuery ad hoc1
ReplProcProcedura di filtro della replica
TriggerTrigger
VistaVista
Valore predefinitoValore predefinito
UsrTabTabella utente
SysTabTabella di sistema
CheckVincolo CHECK
RegolaRegola

plan_handle

varbinary(64)

Identificatore del piano in memoria. Si tratta di un identificatore temporaneo, che rimane costante solo se il piano rimane nella cache. È possibile utilizzare questo valore con le funzioni a gestione dinamica seguenti:

sys.dm_exec_sql_text

sys.dm_exec_query_plan

sys.dm_exec_plan_attributes

pool_id

int

ID del pool di risorse in base a cui viene rilevato l'utilizzo della memoria del piano.

1  Fa riferimento a istruzioni Transact-SQL inviate come eventi di linguaggio tramite osql o sqlcmd, anziché come chiamate di procedure remote.

Autorizzazioni

È richiesta l'autorizzazione VIEW SERVER STATE nel server.

Esempi

A. Restituzione del testo del batch per le voci memorizzate nella cache che vengono riutilizzate

Nell'esempio seguente viene restituito il testo SQL di tutte le voci memorizzate nella cache utilizzate più di una volta.

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. Restituzione dei piani di query per tutti i trigger memorizzati nella cache

Nell'esempio seguente vengono restituiti i piani di query di tutti i trigger memorizzati nella 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. Restituzione delle opzioni SET con cui è stato compilato il piano

Nell'esempio seguente vengono restituite le opzioni SET con cui è stato compilato il piano. Viene inoltre restituito il valore sql_handle per il piano. L'operatore PIVOT viene utilizzato per restituire gli attributi set_options e sql_handle come colonne anziché come righe nell'output. Per ulteriori informazioni sul valore restituito in set_options, vedere 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. Restituzione della suddivisione di memoria per tutti i piani compilati memorizzati nella cache

Nell'esempio seguente viene restituita una suddivisione della memoria utilizzata da tutti i piani compilati nella 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