sys.dm_exec_plan_attributes (Transact-SQL)
Si applica a: 
SQL Server
Restituisce una riga per ogni attributo del piano specificato dall'handle di piano. È possibile utilizzare questa funzione con valori di tabella per recuperare informazioni dettagliate su un particolare piano, come i valori di chiave nella cache o il numero corrente di esecuzioni simultanee del piano.
Nota
Alcune delle informazioni restituite tramite questa funzione sono mappate alla vista di compatibilità con le versioni precedenti di sys.syscacheobjects .
Sintassi
sys.dm_exec_plan_attributes ( plan_handle )
Argomenti
plan_handle
Viene identificato in modo univoco un piano di query per un batch eseguito il cui piano risiede nella cache dei piani. plan_handle is varbinary(64). L'handle di piano può essere ottenuto dalla vista a gestione dinamica sys.dm_exec_cached_plans .
Tabella restituita
| Nome colonna | Tipo di dati | Descrizione |
|---|---|---|
| Attributo | varchar(128) | Nome dell'attributo associato al piano. La tabella immediatamente sotto questa elenca gli attributi possibili, i relativi tipi di dati e le relative descrizioni. |
| value | sql_variant | Valore dell'attributo associato al piano. |
| is_cache_key | bit | Indica se l'attributo viene utilizzato come parte della chiave di ricerca nella cache per il piano. |
Dalla tabella precedente, l'attributo può avere i valori seguenti:
| Attributo | Tipo di dati | Descrizione |
|---|---|---|
| set_options | int | Indica i valori delle opzioni con cui è stato compilato il piano. |
| objectid | int | Una delle chiavi principali utilizzate per la ricerca di un oggetto nella cache. Si tratta dell'ID oggetto archiviato in sys.objects per gli oggetti di database (procedure, viste, trigger e così via). Per i piani di tipo ad hoc o preparati, questo attributo corrisponde a un hash interno del testo del batch. |
| dbid | int | ID del database contenente l'entità alla quale fa riferimento il piano. Per i piani ad hoc o preparati, corrisponde all'ID del database da cui viene eseguito il batch. |
| dbid_execute | int | Per gli oggetti di sistema archiviati nel database delle risorse , l'ID del database da cui viene eseguito il piano memorizzato nella cache. In tutti gli altri casi è 0. |
| user_id | int | Il valore -2 indica che il batch inviato non dipende dalla risoluzione implicita del nome e può essere condiviso da diversi utenti. Questo è il metodo preferito. Qualsiasi altro valore rappresenta l'ID dell'utente che invia la query al database. |
| language_id | smallint | ID della lingua della connessione in cui è stato creato l'oggetto della cache. Per altre informazioni, vedere sys.syslanguages (Transact-SQL). |
| date_format | smallint | Formato della data della connessione in cui è stato creato l'oggetto della cache. Per altre informazioni, vedere SET DATEFORMAT (Transact-SQL). |
| date_first | tinyint | Primo valore di data. Per altre informazioni, vedere SET DATEFIRST (Transact-SQL). |
| compat_level | tinyint | Rappresenta il livello di compatibilità impostato nel database nel cui contesto è stato compilato il piano di query. Il livello di compatibilità restituito è il livello di compatibilità del contesto di database corrente per le istruzioni ad hoc e non è interessato dall'hint per la query QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n. Per le istruzioni contenute in una stored procedure o una funzione corrisponde al livello di compatibilità del database in cui viene creata la stored procedure o la funzione. |
| stato | int | Bit di stato interni che fanno parte della chiave di ricerca nella cache. |
| required_cursor_options | int | Opzioni di cursore specificate dall'utente, ad esempio il tipo di cursore. |
| acceptable_cursor_options | int | Le opzioni del cursore che SQL Server può convertire in modo implicito per supportare l'esecuzione dell'istruzione. Ad esempio, l'utente potrebbe specificare un cursore dinamico, ma Query Optimizer è autorizzato a convertire questo tipo di cursore in un cursore statico. |
| merge_action_type | smallint | Il tipo di piano di esecuzione del trigger utilizzato come risultato di un'istruzione MERGE. 0 indicano un piano non-trigger, un piano del trigger che non viene eseguito come risultato di un'istruzione MERGE o un piano del trigger che viene eseguito come risultato di un'istruzione MERGE in cui viene specificata solo un'azione DELETE. 1 indica un piano di trigger INSERT che è in esecuzione come risultato di un'istruzione MERGE. 2 indica un piano di trigger UPDATE in esecuzione come risultato di un'istruzione MERGE. 3 indica un piano di trigger DELETE che viene eseguito come risultato di un'istruzione MERGE che contiene un'azione INSERT o UPDATE corrispondente. Per i trigger nidificati eseguiti da azioni a catena, questo valore è l'azione dell'istruzione MERGE che provoca la propagazione. |
| is_replication_specific | int | Rappresenta che la sessione da cui è stato compilato questo piano è una che è connessa all'istanza di SQL Server usando una proprietà di connessione non documentata che consente al server di identificare la sessione come creata dai componenti di replica, in modo che il comportamento di determinati aspetti funzionali del server venga modificato in base a quanto previsto da tale componente di replica. |
| optional_spid | smallint | La connessione session_id (spid) diventa parte della chiave della cache per ridurre il numero di ricompilato. Ciò impedisce la ricompilazione per il riutilizzo di una singola sessione di un piano che include tabelle temporanee non associate dinamicamente. |
| optional_clr_trigger_dbid | int | Popolato solo nel caso di un trigger CLR DML. ID del database contenente l'entità. Per qualsiasi altro tipo di oggetto, restituisce zero. |
| optional_clr_trigger_objid | int | Popolato solo nel caso di un trigger CLR DML. ID oggetto archiviato in sys.objects. Per qualsiasi altro tipo di oggetto, restituisce zero. |
| parent_plan_handle | varbinary(64) | Sempre NULL. |
| is_azure_user_plan | tinyint | 1 per le query eseguite in un database SQL di Azure da una sessione avviata da un utente. 0 per le query eseguite da una sessione non avviata da un utente finale, ma da applicazioni in esecuzione dall'infrastruttura di Azure che eseguono query per altri scopi di raccolta dei dati di telemetria o esecuzione di attività amministrative. I clienti non vengono addebitati costi per le risorse utilizzate dalle query in cui is_azure_user_plan = 0. solo database SQL di Azure. |
| inuse_exec_context | int | Numero di batch in esecuzione che utilizzano il piano di query. |
| free_exec_context | int | Numero di contesti di esecuzione memorizzati nella cache per il piano di query, attualmente inutilizzati. |
| hits_exec_context | int | Numero di riutilizzi del contesto di esecuzione recuperato dalla cache dei piani, con conseguente risparmio dell'overhead correlato alla ricompilazione dell'istruzione SQL. Il valore rappresenta un'aggregazione per tutti i batch eseguiti finora. |
| misses_exec_context | int | Numero di volte in cui non è stato possibile trovare un contesto di esecuzione nella cache dei piani, con conseguente creazione di un nuovo contesto di esecuzione per l'esecuzione del batch. |
| removed_exec_context | int | Numero di contesti di esecuzione rimossi a causa di richieste di memoria eccessive per il piano memorizzato nella cache. |
| inuse_cursors | int | Numero di batch in esecuzione che contengono uno o più cursori che utilizzano il piano memorizzato nella cache. |
| free_cursors | int | Numero di cursori inattivi o liberi per il piano memorizzato nella cache. |
| hits_cursors | int | Numero di riutilizzi di un cursore inattivo ottenuto dal piano memorizzato nella cache. Il valore rappresenta un'aggregazione per tutti i batch eseguiti finora. |
| misses_cursors | int | Numero di volte in cui non è stato possibile trovare un cursore inattivo nella cache. |
| removed_cursors | int | Numero di cursori rimossi a causa di richieste di memoria eccessive per il piano memorizzato nella cache. |
| sql_handle | varbinary(64) | Handle SQL per il batch. |
Autorizzazioni
In SQL Server è richiesta VIEW SERVER STATE l'autorizzazione.
In database SQL di Azure obiettivi di servizio Basic, S0 e S1 e per i database nei pool elastici, è necessario l'account amministratore del server o l'account amministratore di Microsoft Entra. Per tutti gli altri obiettivi di servizio database SQL, l'autorizzazione VIEW DATABASE STATE è necessaria nel database.
Autorizzazioni per SQL Server 2022 e versioni successive
È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.
Osservazioni:
Opzioni SET
Le copie dello stesso piano compilato possono differire solo per il valore nella colonna set_options . Ciò indica che connessioni diverse utilizzano set di opzioni SET diversi per la stessa query. L'utilizzo di set diversi di opzioni è in genere poco consigliabile perché può causare compilazioni aggiuntive, un minore riutilizzo del piano e un aumento delle dimensioni della cache dei piani in seguito alla presenza di più copie dei piani.
Valutazione delle opzioni SET
Per convertire il valore restituito in set_options alle opzioni con cui è stato compilato il piano, sottrarre i valori dal valore set_options , a partire dal valore più grande possibile, fino a raggiungere 0. Ogni valore sottratto corrisponde a un'opzione utilizzata nel piano di query. Ad esempio, se il valore in set_options è 251, le opzioni con cui è stato compilato il piano sono ANSI_NULL_DFLT_ON (128), QUOTED_IDENTIFIER (64), ANSI_NULLS(32), ANSI_WARNINGS (16), CONCAT_NULL_YIELDS_NULL (8), Piano parallelo (2) e ANSI_PADDING (1).
| Opzione | Valore |
|---|---|
| ANSI_PADDING | 1 |
| ParallelPlan Indica che le opzioni di parallelismo del piano sono state modificate. |
2 |
| FORCEPLAN | 4 |
| CONCAT_NULL_YIELDS_NULL | 8 |
| ANSI_WARNINGS | 16 |
| ANSI_NULLS | 32 |
| QUOTED_IDENTIFIER | 64 |
| ANSI_NULL_DFLT_ON | 128 |
| ANSI_NULL_DFLT_OFF | 256 |
| NoBrowseTable Indica che il piano non utilizza una tabella di lavoro per implementare un'operazione FOR BROWSE. |
512 |
| TriggerOneRow Indica che il piano contiene l'ottimizzazione della singola riga per le tabelle delta del trigger AFTER. |
1024 |
| ResyncQuery Indica che la query è stata inviata da stored procedure di sistema interne. |
2048 |
| ARITH_ABORT | 4096 |
| NUMERIC_ROUNDABORT | 8192 |
| DATEFIRST | 16384 |
| DATEFORMAT | 32768 |
| LanguageID | 65536 |
| UPON Indica che l'opzione di database PARAMETERIZATION era impostata su FORCED al momento della compilazione del piano. |
131072 |
| ROWCOUNT | Si applica a: SQL Server 2012 (11.x) e versioni successive 262144 |
Cursori
I cursori inattivi vengono memorizzati nella cache in un piano compilato in modo che la memoria utilizzata per archiviare il cursore possa essere riutilizzata da utenti simultanei dei cursori. Si supponga, ad esempio, che un batch dichiari e utilizzi un cursore senza deallocarlo. Se due utenti eseguono lo stesso batch, saranno presenti due cursori attivi. Dopo la deallocazione dei cursori, potenzialmente in batch diversi, la memoria utilizzata per archiviare il cursore viene assegnata alla cache e non rilasciata. Questo elenco dei cursori inattivi viene mantenuto nel piano compilato. In occasione della successiva esecuzione del batch, la memoria per il cursore nella cache verrà riutilizzata e inizializzata in modo appropriato come cursore attivo.
Valutazione delle opzioni di cursore
Per convertire il valore restituito in required_cursor_options e acceptable_cursor_options alle opzioni con cui è stato compilato il piano, sottrarre i valori dal valore della colonna, a partire dal valore massimo possibile, fino a raggiungere 0. Ogni valore sottratto corrisponde a un'opzione di cursore utilizzata nel piano di query.
| Opzione | Valore |
|---|---|
| None | 0 |
| INSENSITIVE | 1 |
| SCROLL | 2 |
| READ ONLY | 4 |
| FOR UPDATE | 8 |
| LOCAL | 16 |
| GLOBAL | 32 |
| FORWARD_ONLY | 64 |
| KEYSET | 128 |
| DYNAMIC | 256 |
| SCROLL_LOCKS | 512 |
| OPTIMISTIC | 1024 |
| STATIC | 2048 |
| FAST_FORWARD | 4096 |
| IN PLACE | 8192 |
| FOR select_statement | 16384 |
Esempi
R. Restituzione degli attributi per un piano specifico
Nell'esempio seguente vengono restituiti tutti gli attributi per un piano specificato. Viene prima di tutto eseguita una query sulla vista a gestione dinamica sys.dm_exec_cached_plans per ottenere l'handle del piano specificato. Nella seconda query, sostituire <plan_handle> con il valore di handle di piani restituito dalla prima query.
SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype
FROM sys.dm_exec_cached_plans;
GO
SELECT attribute, [value], is_cache_key
FROM sys.dm_exec_plan_attributes(<plan_handle>);
GO
B. Restituzione delle opzioni SET per i piani compilati e dell'handle SQL per i piani memorizzati nella cache
Nell'esempio seguente viene restituito un valore che rappresenta le opzioni con cui è stato compilato ogni piano. Inoltre, viene restituito l'handle SQL per tutti i piani memorizzati nella cache.
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
Vedi anche
Funzioni a gestione dinamica e DMV (Transact-SQL)
Funzioni e viste a gestione dinamica relative all'esecuzione (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.databases (Transact-SQL)
sys.objects (Transact-SQL)