sys.dm_db_index_operational_stats (Transact-SQL)

Si applica a:SQL Server Database SQL di AzureIstanza gestita di SQL di AzureDatabase SQL in Microsoft Fabric

Restituisce le statistiche di accesso, blocco e latch dei dati di livello inferiore per ogni partizione di una tabella o di un indice in un database.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

sys.dm_db_index_operational_stats (
{ database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | 0 | NULL | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
)

Argomenti

database_id | NULL | 0 | DEFAULT

ID del database. database_id è smallint. Gli input validi sono il numero di ID di un database, NULL, 0 o DEFAULT. Il valore predefinito è 0. NULL, 0 e DEFAULT sono valori equivalenti in questo contesto.

Specificare NULL per restituire informazioni per tutti i database nell'istanza di SQL Server. Se si specifica NULL per database_id, è necessario specificare anche NULL per object_id, index_id e partition_number.

È possibile specificare la funzione predefinita DB_ID.

object_id | NULL | 0 | DEFAULT

ID oggetto della tabella o della vista su cui si trova l'indice. object_id è int.

Gli input validi sono il numero di ID di una tabella o vista, NULL, 0 o DEFAULT. Il valore predefinito è 0. NULL, 0 e DEFAULT sono valori equivalenti in questo contesto.

Specificare NULL per restituire informazioni per tutte le tabelle e le viste nel database specificato. Se si specifica NULL per object_id, è necessario specificare anche NULL per index_id e partition_number.

index_id | 0 | NULL | -1 | DEFAULT

ID dell'indice. index_id è int. Gli input validi sono il numero ID di un indice, 0 se object_id è un heap, NULL, -1 o DEFAULT. Il valore predefinito è -1. NULL, -1 e DEFAULT sono valori equivalenti in questo contesto.

Specificare NULL per restituire informazioni per tutti gli indici per una tabella o una vista di base. Se si specifica NULL per index_id, è necessario specificare anche NULL per partition_number.

partition_number | NULL | 0 | DEFAULT

Numero di partizione nell'oggetto. partition_number è int. Gli input validi sono i partition_number di un indice o di un heap, NULL, 0 o DEFAULT. Il valore predefinito è 0. NULL, 0 e DEFAULT sono valori equivalenti in questo contesto.

Specificare NULL per restituire informazioni per tutte le partizioni dell'indice o dell'heap.

partition_number è basato su 1. Un indice o un heap non partizionato ha partition_number impostato su 1.

Tabella restituita

Nome colonna	Tipo di dati	Descrizione
`database_id`	smallint	ID del database. In database SQL di Azure i valori sono univoci all'interno di un database singolo o di un pool elastico, ma non all'interno di un server logico.
`object_id`	int	ID della tabella o vista. Per altre informazioni, vedere sys.objects (Transact-SQL).
`index_id`	int	ID dell'indice o dell'heap. Per altre informazioni, vedere sys.indexes (Transact-SQL).
`partition_number`	int	Numero di partizione in base 1 all'interno dell'indice o heap. Per altre informazioni, vedere sys.partitions (Transact-SQL).
`hobt_id`	bigint	ID dell'heap di dati o del set di righe albero B che tiene traccia dei dati interni per un indice columnstore. NULL: non si tratta di un set di righe columnstore interno. Per altre informazioni, vedere sys.internal_partitions (Transact-SQL).
`leaf_insert_count`	bigint	Conteggio cumulativo degli inserimenti a livello foglia. Per altre informazioni sui livelli di indice, vedere Architettura e guida alla progettazione degli indici.
`leaf_delete_count`	bigint	Conteggio cumulativo delle eliminazioni a livello foglia. `leaf_delete_count` viene incrementato solo per i record eliminati che non vengono contrassegnati come fantasma per primi. Per i record eliminati che vengono prima visualizzati in modo fantasma, `leaf_ghost_count` viene invece incrementato.
`leaf_update_count`	bigint	Conteggio cumulativo degli aggiornamenti a livello foglia.
`leaf_ghost_count`	bigint	Conteggio cumulativo delle righe a livello foglia contrassegnate come eliminate, ma non ancora rimosse. Questo conteggio non include record che vengono eliminati immediatamente senza essere contrassegnati come fantasma. Un thread di pulizia rimuove le righe fantasma a intervalli impostati. Questo valore non include righe fantasma mantenute a causa di una transazione snapshot in sospeso.
`nonleaf_insert_count`	bigint	Conteggio cumulativo degli inserimenti sopra il livello foglia. Si applica solo agli indici ad albero B. 0 per gli indici heap o columnstore.
`nonleaf_delete_count`	bigint	Conteggio cumulativo delle eliminazioni sopra il livello foglia. Si applica solo agli indici ad albero B. 0 per gli indici heap o columnstore.
`nonleaf_update_count`	bigint	Conteggio cumulativo degli aggiornamenti sopra il livello foglia. Si applica solo agli indici ad albero B. 0 per gli indici heap o columnstore.
`leaf_allocation_count`	bigint	Conteggio cumulativo delle allocazioni di pagine a livello foglia nell'indice o nell'heap. Per un indice un'allocazione di pagina corrisponde a una suddivisione di pagina.
`nonleaf_allocation_count`	bigint	Conteggio cumulativo delle allocazioni di pagina provocate da suddivisioni di pagina sopra il livello foglia. Si applica solo agli indici ad albero B. 0 per gli indici heap o columnstore.
`leaf_page_merge_count`	bigint	Conteggio cumulativo delle unioni di pagina in corrispondenza del livello foglia. Sempre 0 per gli indici columnstore.
`nonleaf_page_merge_count`	bigint	Conteggio cumulativo delle unioni di pagina sopra il livello foglia. Si applica solo agli indici ad albero B. 0 per gli indici heap o columnstore.
`range_scan_count`	bigint	Conteggio cumulativo delle analisi di intervallo e tabella avviate nell'indice o nell'heap.
`singleton_lookup_count`	bigint	Conteggio cumulativo dei recuperi di singole righe dall'indice o heap.
`forwarded_fetch_count`	bigint	Conteggio delle righe recuperate tramite un record di inoltro. Si applica solo agli heap, 0 per gli indici albero B.
`lob_fetch_in_pages`	bigint	Conteggio cumulativo di pagine LOB (Large Object) recuperate da un'unità `LOB_DATA` di allocazione. Queste pagine contengono dati archiviati in colonne di tipo `text`, , `ntextimage`, `varbinary(max)nvarchar(max)varchar(max)xml`e .`json` Per altre informazioni, vedere Tipi di dati (Transact-SQL).
`lob_fetch_in_bytes`	bigint	Conteggio cumulativo dei byte di dati LOB recuperati.
`lob_orphan_create_count`	bigint	Conteggio cumulativo dei valori LOB isolati (orfani) creati per le operazioni bulk. Si applica solo agli heap e agli indici cluster ad albero B, 0 per gli indici columnstore e non cluster.
`lob_orphan_insert_count`	bigint	Conteggio cumulativo dei valori LOB isolati (orfani) inseriti durante le operazioni bulk. Si applica solo agli heap e agli indici cluster ad albero B, 0 per gli indici columnstore e non cluster.
`row_overflow_fetch_in_pages`	bigint	Conteggio cumulativo delle pagine di dati di overflow delle righe recuperate da un'unità `ROW_OVERFLOW_DATA` di allocazione. Queste pagine contengono dati archiviati in colonne di tipo `varchar(n)`, `nvarchar(n)`, `varbinary(n)`e `sql_variant` per righe di grandi dimensioni.
`row_overflow_fetch_in_bytes`	bigint	Conteggio cumulativo dei byte di dati di overflow della riga recuperati.
`column_value_push_off_row_count`	bigint	Conteggio cumulativo dei valori di colonna per i dati LOB e di overflow della riga spostati all'esterno di righe per adattare una riga inserita o aggiornata all'interno di una pagina.
`column_value_pull_in_row_count`	bigint	Conteggio cumulativo dei valori di colonna per i dati LOB e di overflow della riga esclusi dalla riga. Ciò si verifica quando un'operazione di aggiornamento libera spazio in un record e offre l'opportunità di eseguire il pull di uno o più valori fuori riga da un'unità `LOB_DATA` di allocazione o `ROW_OVERFLOW_DATA` all'unità `IN_ROW_DATA` di allocazione.
`row_lock_count`	bigint	Numero cumulativo di blocchi di riga richiesti.
`row_lock_wait_count`	bigint	Numero cumulativo di attese del motore di database su un blocco di riga.
`row_lock_wait_in_ms`	bigint	Numero totale di millisecondi di attesa del motore di database in un blocco di riga.
`page_lock_count`	bigint	Numero cumulativo di blocchi di pagina richiesti.
`page_lock_wait_count`	bigint	Numero cumulativo di attese del motore di database in un blocco di pagina.
`page_lock_wait_in_ms`	bigint	Numero totale di millisecondi di attesa del motore di database in un blocco di pagina.
`index_lock_promotion_attempt_count`	bigint	Numero cumulativo di tentativi di escalation dei blocchi motore di database.
`index_lock_promotion_count`	bigint	Numero cumulativo di volte in cui il motore di database i blocchi inoltrati.
`page_latch_wait_count`	bigint	Numero cumulativo di attese del motore di database per l'acquisizione di un latch.
`page_latch_wait_in_ms`	bigint	Numero cumulativo di millisecondi di attesa del motore di database per acquisire un latch.
`page_io_latch_wait_count`	bigint	Numero cumulativo di attese del motore di database in un latch di I/O di pagina.
`page_io_latch_wait_in_ms`	bigint	Numero cumulativo di millisecondi di attesa del motore di database in un latch di I/O di pagina.
`tree_page_latch_wait_count`	bigint	Subset di `page_latch_wait_count` che include solo le pagine ad albero B di livello superiore. Sempre 0 per un heap o un indice columnstore.
`tree_page_latch_wait_in_ms`	bigint	Subset di `page_latch_wait_in_ms` che include solo le pagine ad albero B di livello superiore. Sempre 0 per un heap o un indice columnstore.
`tree_page_io_latch_wait_count`	bigint	Subset di `page_io_latch_wait_count` che include solo le pagine ad albero B di livello superiore. Sempre 0 per un heap o un indice columnstore.
`tree_page_io_latch_wait_in_ms`	bigint	Subset di `page_io_latch_wait_in_ms` che include solo le pagine ad albero B di livello superiore. Sempre 0 per un heap o un indice columnstore.
`page_compression_attempt_count`	bigint	Numero di pagine valutate per la compressione a livello di PAGINA per una partizione specifica di una tabella, un indice o una vista indicizzata. Include pagine non compresse perché non è stato possibile ottenere risparmi significativi. Sempre 0 per gli indici columnstore.
`page_compression_success_count`	bigint	Numero di pagine di dati valutate compresse utilizzando la compressione di tipo PAGE per partizioni specifiche di una tabella, un indice o una vista indicizzata. Sempre 0 per gli indici columnstore.
`version_generated_inrow`	bigint	Conteggio cumulativo delle versioni in riga con payload generato nell'heap o nell'albero B per un'operazione di aggiornamento, unione o inserimento-over-ghost. Una versione in riga archivia l'immagine di riga precedente (o un diff) direttamente nella riga, evitando un viaggio nell'archivio delle versioni. Questo conteggio è un superset che include le versioni conteggiate da `insert_over_ghost_version_inrow`. Per altre informazioni sulle versioni in riga e fuori riga, vedere Space used by the persistent version store (PVS).

`version_generated_offrow`	bigint	Conteggio cumulativo delle versioni di cui è stato eseguito il push nell'archiviazione fuori riga per un'operazione heap, albero B o LOB, aggiornamento, unione o inserimento-over-ghost. Una versione esterna viene generata quando l'immagine di riga precedente non può essere mantenuta in riga. Questo conteggio è un superset che include le versioni conteggiate da `ghost_version_offrow` e `insert_over_ghost_version_offrow`.
`ghost_version_inrow`	bigint	Numero cumulativo di volte in cui un'eliminazione o un aggiornamento (eseguito come eliminazione seguita da un inserimento) ha contrassegnato la riga esistente come fantasma con informazioni sul controllo delle versioni in riga. La versione in riga archivia solo un timestamp della transazione e un payload di lunghezza zero, in modo che l'annullamento dell'eliminazione richieda solo l'annullamento dell'hosting della riga.The in-row version stores only a transaction timestamp and a zero-length payload, so that undoing the delete only requires unghosting the row.
`ghost_version_offrow`	bigint	Numero cumulativo di volte in cui un'eliminazione o un aggiornamento (eseguito come eliminazione seguita da un inserimento) ha eseguito il push dei dati delle colonne line o LOB esistenti nell'archivio fuori riga, lasciando uno stub nella riga per le informazioni sul controllo delle versioni. Questo contatore viene incrementato insieme `version_generated_offrow` a durante le operazioni fantasma.
`insert_over_ghost_version_inrow`	bigint	Conteggio cumulativo delle versioni in riga con payload generato per un'operazione insert-over-ghost dell'albero B. Un insert-over-ghost si verifica quando una nuova riga viene inserita nello slot di un record fantasma in precedenza, da un'eliminazione esplicita seguita da un inserimento o da un aggiornamento o un'unione implementata come eliminazione seguita da un inserimento. Questo contatore è un subset di `version_generated_inrow`.
`insert_over_ghost_version_offrow`	bigint	Numero cumulativo di volte in cui la riga fantasma esistente è stata inserita nell'archivio all'esterno della riga durante un'operazione di inserimento over fantasma dell'albero B, lasciando uno stub nella riga appena inserita per le informazioni sul controllo delle versioni. Questo contatore è un subset di `version_generated_offrow`.

Nota

Nella documentazione viene usato in modo generico il termine albero B in riferimento agli indici. Negli indici rowstore, il motore di database implementa un albero B+. Ciò non si applica a indici columnstore o a indici in tabelle ottimizzate per la memoria. Per altre informazioni, vedere Architettura e guida per la progettazione degli indici SQL Server e Azure SQL.

Osservazioni:

Questa funzione non restituisce informazioni sugli indici nelle tabelle ottimizzate per la memoria. Per informazioni sugli indici nelle tabelle ottimizzate per la memoria, vedere sys.dm_db_xtp_index_stats (Transact-SQL).

Questa funzione non accetta parametri correlati da CROSS APPLY e OUTER APPLY.

È possibile usare sys.dm_db_index_operational_stats per tenere traccia delle statistiche sulle operazioni di lettura e scrittura dei dati e blocco, latch di pagina e latch di I/O di pagina per una tabella, un indice o una partizione. È possibile identificare le tabelle, gli indici e le partizioni che riscontrano attività o conflitti significativi.

Le statistiche vengono fornite a livello di partizione e sono additivi. Ciò significa che è possibile ottenere statistiche a livello di indice o a livello di tabella scrivendo una query di aggregazione in T-SQL. Per altre informazioni, vedere l'esempio Relativo all'analisi dell'indice e alla ricerca di tutte le tabelle .

Per analizzare le statistiche sulle operazioni di lettura e scrittura per una tabella, un indice o una partizione, usare queste colonne:

leaf_insert_count
leaf_delete_count
leaf_update_count
leaf_ghost_count
range_scan_count
singleton_lookup_count

Per identificare la contesa di latch, usare queste colonne:

page_latch_wait_count
page_latch_wait_in_ms

Per identificare la contesa di blocco, usare queste colonne:

row_lock_count
page_lock_count
row_lock_wait_in_ms
page_lock_wait_in_ms

Per analizzare le statistiche di I/O fisiche, usare queste colonne:

page_io_latch_wait_count
page_io_latch_wait_in_ms

Osservazioni sulla colonna

I valori nelle colonne lob_fetch_in_pages e lob_fetch_in_bytes possono essere maggiori di zero per gli indici non cluster che contengono una o più colonne LOB come colonne incluse. Per altre informazioni, vedere Creare indici con colonne incluse. Analogamente, i valori nelle colonne row_overflow_fetch_in_pages e row_overflow_fetch_in_bytes possono essere maggiori di 0 per gli indici non cluster se l'indice contiene righe di grandi dimensioni.

Come vengono reimpostati i contatori nella cache dei metadati

I dati restituiti da sys.dm_db_index_operational_stats esistono solo se è disponibile un oggetto cache dei metadati che rappresenta l'heap o l'albero B. Questi dati non sono persistenti. Ciò significa che non è possibile utilizzare questi contatori per determinare in modo definitivo se è stato usato o meno un indice o se è stato usato l'ultimo indice. Usare invece sys.dm_db_index_usage_stats (Transact-SQL).

I valori per ogni colonna numerica vengono impostati su zero ogni volta che i metadati per l'heap o l'albero B vengono inseriti nella cache dei metadati. Le statistiche vengono accumulate fino a quando l'oggetto cache non viene rimosso dalla cache dei metadati. Un heap attivo o albero B ha in genere i metadati nella cache e i conteggi cumulativi riflettono l'attività dall'ultimo avvio dell'istanza del motore di database. I metadati per un heap meno attivo o un albero B potrebbero spostarsi all'interno e all'esterno della cache durante l'uso, in particolare se l'istanza del motore di database è sotto pressione di memoria. Di conseguenza, le statistiche operative dell'indice potrebbero talvolta non essere riflesse in sys.dm_db_index_operational_stats. Non è uno scenario comune.

Le statistiche vengono rimosse dalla cache e non vengono più segnalate da questa funzione se viene eliminata una tabella o un indice o se una partizione viene troncata. Altre operazioni DDL sull'indice potrebbero causare la reimpostazione del valore delle statistiche su zero.

Uso delle funzioni di sistema per specificare i valori dei parametri

È possibile usare le funzioni Transact-SQL DB_ID e OBJECT_ID per specificare un valore per i parametri database_id e object_id . Se si passano valori non validi a queste funzioni, tuttavia, si potrebbero provocare risultati imprevisti. Assicurarsi sempre che venga restituito un ID valido quando si usa DB_ID o OBJECT_ID. Per altre informazioni, vedere Restituire informazioni per una tabella specificata.

Autorizzazioni

Sono richieste le autorizzazioni seguenti:

CONTROL autorizzazione per l'oggetto specificato all'interno del database
VIEW DATABASE STATE o VIEW DATABASE PERFORMANCE STATE autorizzazione per restituire informazioni su tutti gli oggetti all'interno del database specificato, quando non viene specificato un valore per @object_id .
VIEW SERVER STATE o VIEW SERVER PERFORMANCE STATE autorizzazione per restituire informazioni su tutti i database, quando non viene specificato un valore per @database_id .

Concedere o VIEW SERVER PERFORMANCE STATE consentire la restituzione VIEW DATABASE STATE di tutti gli oggetti nel database, indipendentemente dalle CONTROL autorizzazioni negate per oggetti specifici.

Negando o VIEW SERVER PERFORMANCE STATE non consentendo la restituzione VIEW DATABASE STATE di tutti gli oggetti nel database, indipendentemente dalle CONTROL autorizzazioni concesse per oggetti specifici.

Per altre informazioni, vedere Funzioni e viste a gestione dinamica (Transact-SQL).

Esempi

Restituire informazioni per una tabella specificata

Il seguente esempio restituisce informazioni per tutti gli indici e partizioni della Person.Address tabella nel database AdventureWorks2025.

Importante

Quando si usano le funzioni DB_ID di Transact-SQL e OBJECT_ID per restituire un valore di parametro, assicurarsi sempre che venga restituito un ID valido. Se non è possibile trovare il nome del database o dell'oggetto, ad esempio quando non esistono o non sono stati digitati correttamente, entrambe le funzioni restituiscono NULL. La sys.dm_db_index_operational_stats funzione interpreta NULL come valore jolly che specifica tutti i database o tutti gli oggetti. Poiché può trattarsi di un'operazione accidentale, gli esempi riportati in questa sezione dimostrano la procedura sicura per determinare gli ID di database e oggetti.

DECLARE @db_id int = DB_ID(N'AdventureWorks2025');
DECLARE @object_id int = OBJECT_ID(N'AdventureWorks2025.Person.Address');

SELECT *
FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL)
WHERE @db_id IS NOT NULL AND @object_id IS NOT NULL;

Restituire informazioni per tutte le tabelle e gli indici

Nell'esempio seguente vengono restituite informazioni per tutte le tabelle e gli indici in un'istanza del motore di database.

SELECT *
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);

Analizza e cerca tutte le tabelle

Nell'esempio seguente vengono aggregati i dati a livello di partizione per restituire la ricerca dell'indice e analizzare le statistiche per tutte le tabelle nel database corrente.

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS object_name,
       COUNT(DISTINCT(index_id)) AS index_count,
       COUNT(DISTINCT(partition_number)) AS partition_count,
       SUM(range_scan_count) AS index_scan_count,
       SUM(singleton_lookup_count) AS index_seek_count
FROM sys.dm_db_index_operational_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT)
GROUP BY OBJECT_SCHEMA_NAME(object_id),
         OBJECT_NAME(object_id)
ORDER BY schema_name, object_name;

Commenti e suggerimenti

Questa pagina è stata utile?

Last updated on 2026-03-31