sys.dm_exec_cached_plans (Transact-SQL)

Articolo
07/05/2024

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.

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)

Condividi tramite