sys.dm_db_index_operational_stats (Transact-SQL)

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Restituisce l'attività di I/O di livello inferiore corrente, blocco, latch e metodo di accesso per ogni partizione di una tabella o di un indice nel database.

Gli indici con ottimizzazione per la memoria non vengono visualizzati in questa DMV.

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 le informazioni memorizzate nella cache 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 le informazioni memorizzate nella cache per tutti gli indici per una vista o tabella 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 partion_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 memorizzate nella cache 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.
index_id	int	ID dell'indice o dell'heap. 0 = heap
partition_number	int	Numero di partizione in base 1 all'interno dell'indice o heap.
hobt_id	bigint	Si applica a: SQL Server 2016 (13.x) e versioni successive, database SQL di Azure. 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 al livello foglia.
leaf_delete_count	bigint	Conteggio cumulativo delle eliminazioni al livello foglia. leaf_delete_count viene incrementato solo per i record eliminati che non vengono contrassegnati come fantasma per primi. Per i record eliminati prima di tutto fantasma, leaf_ghost_count viene incrementato.
leaf_update_count	bigint	Conteggio cumulativo degli aggiornamenti al livello foglia.
leaf_ghost_count	bigint	Conteggio cumulativo delle righe al livello foglia contrassegnate come eliminate ma non ancora rimosse. Questo conteggio non include record che vengono eliminati immediatamente senza essere contrassegnati come fantasma. Queste righe vengono rimosse da un thread di pulizia a intervalli impostati. Questo valore non include righe memorizzate a causa di una transazione di isolamento dello snapshot in sospeso.
nonleaf_insert_count	bigint	Conteggio cumulativo degli inserimenti sopra il livello foglia. 0 = heap o columnstore
nonleaf_delete_count	bigint	Conteggio cumulativo delle eliminazioni sopra il livello foglia. 0 = heap o columnstore
nonleaf_update_count	bigint	Conteggio cumulativo degli aggiornamenti sopra il livello foglia. 0 = heap o columnstore
leaf_allocation_count	bigint	Conteggio cumulativo delle allocazioni di pagina al livello foglia nell'indice o 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. 0 = heap o columnstore
leaf_page_merge_count	bigint	Conteggio cumulativo delle unioni di pagina in corrispondenza del livello foglia. Sempre 0 per un indice columnstore.
nonleaf_page_merge_count	bigint	Conteggio cumulativo delle unioni di pagina sopra il livello foglia. 0 = 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. 0 = Indici
lob_fetch_in_pages	bigint	Conteggio cumulativo delle pagine LOB recuperate dall'unità di allocazione LOB_DATA. Queste pagine contengono dati archiviati in colonne di tipo text, ntext, image, varchar(max), nvarchar(max), varbinary(max)e xml. 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. 0 = Indice non cluster
lob_orphan_insert_count	bigint	Conteggio cumulativo dei valori LOB isolati (orfani) inseriti durante le operazioni bulk. 0 = Indice non cluster
row_overflow_fetch_in_pages	bigint	Conteggio cumulativo delle pagine di dati di overflow della riga recuperate dall'unità di allocazione ROW_OVERFLOW_DATA. Queste pagine contengono dati archiviati in colonne di tipo varchar(n), nvarchar(n), varbinary(n)e sql_variant di cui è stato eseguito il push fuori riga.
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 includere uno o più valori all'esterno di righe dall'unità di allocazione LOB_DATA o ROW_OVERFLOW_DATA nell'unità di allocazione IN_ROW_DATA.
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 volte in cui il motore di database è stato atteso, a causa della contesa di latch.
page_latch_wait_in_ms	bigint	Numero cumulativo di millisecondi di attesa del motore di database a causa di contesa di latch.
page_io_latch_wait_count	bigint	Numero cumulativo di attese del motore di database in un latch di pagina di I/O.
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 di tipo PAGE per partizioni specifiche di una tabella, un indice o una vista indicizzata. Sono incluse le pagine che non sono state compresse perché la compressione non avrebbe comportato risparmi significativi. Sempre 0 per un indice 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 un indice columnstore.

Osservazioni:

Questo oggetto a gestione dinamica non accetta parametri correlati da CROSS APPLY e OUTER APPLY.

È possibile usare sys.dm_db_index_operational_stats per tenere traccia dell'intervallo di tempo in cui gli utenti devono attendere la lettura o la scrittura in una tabella, un indice o una partizione e identificare le tabelle o gli indici che riscontrano attività di I/O significative o aree sensibili.

Utilizzare le colonne seguenti per identificare le aree di contesa.

Per analizzare un modello di accesso comune alla tabella o alla partizione di indice, usare queste colonne:

• leaf_insert_count

• leaf_delete_count

• leaf_update_count

• leaf_ghost_count

• range_scan_count

• singleton_lookup_count

Per identificare le contese a livello di latch e blocchi, utilizzare le colonne seguenti:

• page_latch_wait_count e page_latch_wait_in_ms

  Queste colonne indicano se è presente una contesa di latch nell'indice o nell'heap e specificano l'importanza di tale contesa.

• row_lock_count e page_lock_count

  Queste colonne indicano quante volte il motore di database ha tentato di acquisire blocchi di riga e di pagina.

• row_lock_wait_in_ms e page_lock_wait_in_ms

  Queste colonne indicano se è presente una contesa di blocchi nell'indice o heap e l'importanza di tale contesa.

Per analizzare le statistiche delle operazioni di I/O fisiche in una partizione di indice o heap

• page_io_latch_wait_count e page_io_latch_wait_in_ms

  Queste colonne indicano se gli I/O fisici sono stati eseguiti per inserire le pagine di indice o heap in memoria e il numero di I/O eseguiti.

Osservazioni relative alle colonne

I valori in lob_orphan_create_count e lob_orphan_insert_count devono essere sempre uguali.

Il valore nelle colonne lob_fetch_in_pages e lob_fetch_in_bytes può essere maggiore 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, il valore nelle colonne row_overflow_fetch_in_pages e row_overflow_fetch_in_bytes può essere maggiore di 0 per gli indici non cluster se l'indice contiene colonne che possono essere spostate all'esterno della riga.

Come vengono reimpostati i contatori nella cache dei metadati

I dati restituiti da sys.dm_db_index_operational_stats esistono solo se è disponibile l'oggetto cache dei metadati che rappresenta l'heap o l'indice. Questi dati non sono persistenti, né consistenti dal punto di vista transazionale. Ciò significa che non è possibile utilizzare questi contatori per determinare se un indice è stato utilizzato o meno oppure quando l'indice è stato utilizzato per l'ultima volta. Per informazioni su questo problema, vedere sys.dm_db_index_usage_stats (Transact-SQL).

I valori di ogni colonna vengono impostati su zero ogni volta che i metadati per l'heap o l'indice vengono inseriti nella cache dei metadati e le statistiche vengono accumulate finché l'oggetto cache non viene rimosso dalla cache dei metadati. Pertanto, un heap o un indice attivo avrà sempre i metadati nella cache e i conteggi cumulativi possono riflettere l'attività dall'ultimo avvio dell'istanza di SQL Server. I metadati di un heap o un indice meno attivo verranno inseriti nella e rimossi dalla cache in base al loro utilizzo. Ne consegue che i valori potrebbero non essere disponibili. L'eliminazione di un indice comporterà la rimozione delle statistiche corrispondenti dalla memoria e tali dati non verranno più rilevati dalla funzione. Altre operazioni DDL nell'indice potrebbero provocare l'azzeramento del valore delle statistiche.

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. Quando si usa DB_ID o OBJECT_ID, verificare sempre che venga restituito un ID valido. Per altre informazioni, vedere la sezione Osservazioni in sys.dm_db_index_physical_stats (Transact-SQL).

Autorizzazioni

Sono richieste le autorizzazioni seguenti:

• CONTROL autorizzazione per l'oggetto specificato all'interno del database

• VIEW DATABASE STATE o VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) autorizzazione per restituire informazioni su tutti gli oggetti all'interno del database specificato, usando il carattere jolly dell'oggetto @object_id = NULL

• VIEW SERVER STATEVIEW SERVER PERFORMANCE STATE (SQL Server 2022) autorizzazione per restituire informazioni su tutti i database usando il carattere jolly del database @database_id = NULL

La concessione VIEW DATABASE STATE consente di restituire tutti gli oggetti nel database, indipendentemente dalle autorizzazioni CONTROL negate per oggetti specifici.

La negazione di non consente la restituzione VIEW DATABASE STATE di tutti gli oggetti nel database, indipendentemente dalle autorizzazioni CONTROL concesse per oggetti specifici. Inoltre, quando viene specificato il carattere jolly @database_id=NULL del database, il database viene omesso.

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

Esempi

R. Visualizzazione di informazioni per una tabella specifica

Nell'esempio seguente vengono restituite informazioni per tutti gli indici e le partizioni della Person.Address tabella nel database AdventureWorks2022. Per eseguire questa query, è necessario disporre almeno dell'autorizzazione CONTROL per la tabella Person.Address.

DECLARE @db_id int;    
DECLARE @object_id int;    
SET @db_id = DB_ID(N'AdventureWorks2022');    
SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address');    
IF @db_id IS NULL     
  BEGIN;    
    PRINT N'Invalid database';    
  END;    
ELSE IF @object_id IS NULL    
  BEGIN;    
    PRINT N'Invalid object';    
  END;    
ELSE    
  BEGIN;    
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);    
  END;    
GO    

B. Restituzione delle informazioni per tutti gli indici e le tabelle

Nell'esempio seguente vengono restituite informazioni per tutte le tabelle e gli indici all'interno dell'istanza di SQL Server. L'esecuzione di questa query richiede l'autorizzazione VIEW SERVER STATE.

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

Vedi anche

Funzioni a gestione dinamica e DMV (Transact-SQL)
Funzioni e viste a gestione dinamica correlate all'indice (Transact-SQL)
Monitoraggio e ottimizzazione delle prestazioni
sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)