sys.dm_db_index_operational_stats (Transact-SQL)

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

Convenzioni della 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 è di tipo 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 presenti nell'istanza di SQL Server. Se si specifica NULL per database_id, è necessario specificare NULL anche per object_id, index_id e partition_number.

  È possibile specificare la funzione predefinita DB_ID. Quando si utilizza DB_ID senza specificare un nome di database, il livello di compatibilità del database corrente deve essere 90.

• object_id | NULL | 0 | DEFAULT
  ID oggetto della tabella o vista in cui si trova l'indice. object_id è di tipo 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 per object_id si specifica NULL, è necessario specificare NULL anche per index_id e partition_number.

• index_id | 0 | NULL | -1 | DEFAULT
  ID dell'indice. index_id è di tipo int. Gli input validi sono il numero di 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 NULL anche per partition_number.

• partition_number | NULL | 0 | DEFAULT
  Numero di partizione nell'oggetto. partition_number è di tipo int. Gli input validi sono are il valore di partion_number di un indice o 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 è in base 1. Per un indice o un heap non partizionato partition_number è impostato su 1.

Tabella restituita

Nome colonna	Tipo di dati	Descrizione
database_id	smallint	ID del database.
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.
leaf_insert_count	bigint	Conteggio cumulativo degli inserimenti al livello foglia.
leaf_delete_count	bigint	Conteggio cumulativo delle eliminazioni al livello foglia.
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. 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 ulteriori 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 spostati all'esterno di righe.
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 volte che Motore di database ha atteso un blocco di riga.
row_lock_wait_in_ms	bigint	Numero totale di millisecondi che Motore di database ha atteso un blocco di riga.
page_lock_count	bigint	Numero cumulativo di blocchi di pagina richiesti.
page_lock_wait_count	bigint	Numero cumulativo di volte che Motore di database ha atteso un blocco di pagina.
page_lock_wait_in_ms	bigint	Numero totale di millisecondi che Motore di database ha atteso un blocco di pagina.
index_lock_promotion_attempt_count	bigint	Numero cumulativo di volte che Motore di database ha tentato di alzare di livello i blocchi.
index_lock_promotion_count	bigint	Numero cumulativo di volte che Motore di database ha alzato di livello i blocchi.
page_latch_wait_count	bigint	Numero cumulativo di volte che Motore di database è rimasto in attesa a causa di una contesa di latch.
page_latch_wait_in_ms	bigint	Numero cumulativo di millisecondi che Motore di database è rimasto in attesa a causa di una contesa di latch.
page_io_latch_wait_count	bigint	Numero cumulativo di volte che Motore di database ha atteso un latch della pagina di I/O.
page_io_latch_wait_in_ms	bigint	Numero cumulativo di millisecondi che Motore di database ha atteso un latch di I/O della pagina.
tree_page_latch_wait_count	bigint	Subset di page_latch_wait_count che include solo le pagine dell'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 dell'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 dell'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 dell'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 utilizzare sys.dm_db_index_operational_stats per tenere traccia della quantità di tempo che gli utenti devono attendere per leggere o scrivere in una tabella, un indice o una partizione e per identificare tabelle e indici in cui viene rilevata una significativa attività di I/O o aree critiche.

Utilizzare le colonne seguenti per identificare le aree di contesa.

Per analizzare un comune modello di accesso alla partizione di tabelle o indici, utilizzare le colonne seguenti:

• 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 il numero di volte che Motore di database ha cercato di acquisire blocchi di riga e 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 degli I/O fisici 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 nelle colonne lob_orphan_create_count e lob_orphan_insert_count devono essere sempre uguali.

I valori nelle colonne lob_fetch_in_pages e lob_fetch_in_bytes possono essere maggiori di zero per indici non cluster contenenti una o più colonne LOB come colonne incluse. Per ulteriori informazioni, vedere Creare indici con colonne incluse. In modo analogo, il valore nelle colonne row_overflow_fetch_in_pages e row_overflow_fetch_in_bytes può essere maggiore di 0 per indici non cluster se l'indice include colonne che possono essere spostate all'esterno di righe.

Procedura di ripristino dei contatori nella cache dei metadati

I dati restituiti da sys.dm_db_index_operational_stats esistono solo finché è 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 ulteriori informazioni, 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 possibile che un heap o un indice attivo disponga sempre dei relativi metadati nella cache e che il conteggio cumulativo rifletta 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.

Utilizzo di funzioni di sistema per specificare i valori dei parametri

È possibile utilizzare le funzioni DB_ID e OBJECT_ID di Transact-SQL per specificare un valore per i parametri database_id e object_id. Passando valori non validi a queste funzioni, tuttavia, si possono ottenere risultati imprevisti. Quando si utilizza DB_ID o OBJECT_ID, verificare sempre che venga restituito un ID valido. Per ulteriori informazioni, vedere la sezione Osservazioni in sys.dm_db_index_physical_stats (Transact-SQL).

Autorizzazioni

Sono richieste le autorizzazioni seguenti:

• Autorizzazione CONTROL per l'oggetto specificato nel database

• Autorizzazione VIEW DATABASE STATE per restituire informazioni su tutti gli oggetti nel database specificato tramite il carattere jolly dell'oggetto @object\_id = NULL

• Autorizzazione VIEW SERVER STATE per restituire informazioni su tutti i database tramite il carattere jolly del database @database\_id = NULL

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

La negazione di VIEW DATABASE STATE non consente la restituzione di tutti gli oggetti nel database, indipendentemente dalle eventuali autorizzazioni CONTROL concesse per oggetti specifici. Quando viene specificato il carattere jolly di database @database\_id=NULL, inoltre, il database viene omesso.

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

Esempi

A.Visualizzazione di informazioni per una tabella specifica

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

Importante

In caso di utilizzo delle 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 valore di 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'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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 tutti gli indici nell'istanza di SQL Server. Per eseguire questa query, è necessario disporre dell'autorizzazione VIEW SERVER STATE.

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

Vedere anche

Riferimento

Funzioni e viste a gestione dinamica (Transact-SQL)

Funzioni e viste a gestione dinamica relative agli indici (Transact-SQL)

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)

Concetti

Monitoraggio e ottimizzazione delle prestazioni