Articolo
09/28/2010

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 vista a gestione dinamica 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 vista a gestione dinamica 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 utilizzi dell'oggetto della cache dall'inizio.
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 RuleRegola
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