sys.dm_db_index_operational_stats (Transact-SQL)
Si applica a:
SQL ServerDatabase SQL di AzureIstanza 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.
Nota
sys.dm_db_index_operational_stats non restituisce informazioni sugli indici ottimizzati per la memoria. Per informazioni sull'uso dell'indice ottimizzato per la memoria, vedere sys.dm_db_xtp_index_stats (Transact-SQL).
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 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 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. |
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:
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:
CONTROLautorizzazione per l'oggetto specificato all'interno del databaseVIEW DATABASE STATEoVIEW 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 = NULLVIEW 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.
Importante
Quando si usano le funzioni Transact-SQL DB_ID e OBJECT_ID per restituire un valore di parametro, assicurarsi sempre che venga restituito un ID valido. Se risulta impossibile trovare il nome del database o dell'oggetto, ad esempio quando tali nomi non esistono o sono stati immessi con un'ortografia errata, entrambe le funzioni restituiranno NULL. La funzione sys.dm_db_index_operational_stats interpreta NULL come un carattere 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;
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)