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)