Condividi tramite


sys.dm_exec_cached_plans (Transact-SQL)

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure azure Synapse Analytics Analytics Platform System (PDW)

Restituisce una riga per ogni piano di query memorizzato nella cache da SQL Server per un'esecuzione di query più rapida. È 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.

Nel database SQL di Azure, le viste a gestione dinamica non possono esporre le informazioni che influenzerebbero l'indipendenza del database o le informazioni sugli altri database a cui l'utente dispone di accesso. Per evitare di esporre queste informazioni, ogni riga contenente dati che non appartengono al tenant connesso viene filtrata. Inoltre, i valori nelle colonne memory_object_address e pool_id vengono filtrati; il valore della colonna è impostato su NULL.

Nota

Per chiamare questa operazione da Azure Synapse Analytics o da Platform System (PDW), usare il nome sys.dm_pdw_nodes_exec_cached_plans. Questa sintassi non è supportata da pool SQL serverless in Azure Synapse Analytics.

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.
refcounts int Numero di oggetti della cache che fanno riferimento a questo oggetto della cache. I conteggi di riferimento devono essere almeno 1 perché una voce si trova nella cache.
usecounts int Numeri di volte in cui l'oggetto della cache è stato ricercato. Non incrementato quando le query con parametri trovano un piano nella cache. Può essere incrementato più volte quando si utilizza il piano 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. Questo valore può essere usato con sys.dm_os_memory_objects per ottenere la suddivisione della memoria del piano memorizzato nella cache e con sys.dm_os_memory_cache_entries_entries per ottenere il costo della memorizzazione nella cache della voce.
cacheobjtype nvarchar(34) Tipo di oggetto nella cache. Il valore può essere uno dei seguenti:

Compiled Plan

Compiled Plan Stub

Parse Tree

Extended Proc

CLR Compiled Func

CLR Compiled Proc
objtype nvarchar(16) Tipo di oggetto. Di seguito sono riportati i valori possibili e le relative descrizioni corrispondenti.

Procedura: Stored procedure
Preparato: istruzione preparata
Adhoc: query ad hoc. Fa riferimento a Transact-SQL inviato come eventi del linguaggio usando osql o sqlcmd anziché come chiamate di procedura remota.
ReplProc: Replication-filter-procedure
Trigger: Trigger
Visualizzazione: Visualizzazione
Impostazione predefinita: impostazione predefinita
UsrTab: tabella utente
SysTab: Tabella di sistema
Check: vincolo CHECK
Regola: Regola
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.
pdw_node_id int Si applica a: Azure Synapse Analytics, Piattaforma di analisi (PDW)

Identificatore del nodo in cui è attiva la distribuzione.

1

Autorizzazioni

In SQL Server e Istanza gestita di SQL è richiesta l'autorizzazione VIEW SERVER STATE.

In database SQL obiettivi di servizio Basic, S0 e S1 e per i database nei pool elastici, è necessario l'account amministratore del server, l'account amministratore di Microsoft Entra o l'appartenenza al ruolo del ##MS_ServerStateReader## server. Per tutti gli altri obiettivi di servizio database SQL, è necessaria l'autorizzazione VIEW DATABASE STATE per il database o l'adesione ruolo del server ##MS_ServerStateReader##.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.

Esempi

R. 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 sql_handle restituito anche per il piano. L'operatore PIVOT viene usato per restituire gli set_options attributi e sql_handle come colonne anziché come righe. Per altre 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, 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  

Vedi anche

Funzioni a gestione dinamica e DMV (Transact-SQL)
Funzioni e viste a gestione dinamica relative all'esecuzione (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL)
sys.dm_exec_plan_attributes (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_os_memory_objects (Transact-SQL)
sys.dm_os_memory_cache_entries (Transact-SQL)
FROM (Transact-SQL)