sys.dm_db_index_physical_stats (Transact-SQL)

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

Restituisce informazioni sulle dimensioni e sulla frammentazione per i dati e gli indici della tabella o vista specificata nella motore di database di SQL Server. Per un indice, viene restituita una riga per ogni livello dell'albero B in ogni partizione. Per un heap, viene restituita una riga per l'unità di allocazione IN_ROW_DATA di ogni partizione. Per i dati LOB (Large Object), viene restituita una riga per l'unità LOB_DATA di allocazione di ogni partizione. Se nella tabella sono presenti dati di overflow di righe, viene restituita una riga per l'unità ROW_OVERFLOW_DATA di allocazione in ogni partizione.

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.

sys.dm_db_index_physical_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.

Se si esegue una query sys.dm_db_index_physical_stats su un'istanza del server che ospita una replica secondaria leggibile del gruppo di disponibilità, è possibile che si verifichi un REDO problema di blocco. Ciò è dovuto al fatto che questa visualizzazione a gestione dinamica acquisisce un blocco condiviso con finalità (IS) sulla tabella o sulla vista utente specificata che può bloccare le richieste da parte di un thread per un REDO blocco esclusivo (X) sulla tabella o sulla vista utente.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

database_id | NULL | 0 | DEFAULT

ID del database. database_id è smallint. Gli input validi sono l'ID di un database, NULL, 0o DEFAULT. Il valore predefinito è 0. NULL, 0e 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 NULL anche per object_id, index_id e partition_number.

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

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 l'ID di una tabella e di una vista, , NULL0o DEFAULT. Il valore predefinito è 0. NULL, 0e DEFAULT sono valori equivalenti in questo contesto.

In SQL Server 2016 (13.x) e versioni successive, gli input validi includono anche il nome della coda di Service Broker o il nome della tabella interna della coda. Quando vengono applicati parametri predefiniti( ovvero tutti gli oggetti, tutti gli indici e così via), le informazioni sulla frammentazione per tutte le code vengono incluse nel set di risultati.

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

index_id | 0 | NULL | -1 | DEFAULT

ID dell'indice. index_id è int. Gli input validi sono l'ID di un indice, 0 se object_id è un heap, NULL, -1o DEFAULT. Il valore predefinito è -1. NULL, -1e 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 NULL anche 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, 0o DEFAULT. Il valore predefinito è 0. NULL, 0e DEFAULT sono valori equivalenti in questo contesto.

Specificare NULL per restituire informazioni per tutte le partizioni dell'oggetto proprietario.

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

mode | NULL | DEFAULT

Nome della modalità. mode specifica il livello di analisi utilizzato per ottenere le statistiche. mode è sysname. Gli input validi sono DEFAULT, NULL, LIMITED, SAMPLEDo DETAILED. Il valore predefinito (NULL) è LIMITED.

Tabella restituita

Nome colonna	Tipo di dati	Descrizione
database_id	smallint	ID database della tabella o della vista. 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 oggetto della tabella o della vista in cui è contenuto l'indice.
index_id	int	ID di un indice. 0 = Heap.
partition_number	int	Numero di partizione in base 1 all'interno dell'oggetto proprietario, una tabella, una vista o un indice. 1 = Indice o heap non partizionato.
index_type_desc	nvarchar(60)	Descrizione del tipo di indice: - HEAP - CLUSTERED INDEX - NONCLUSTERED INDEX - PRIMARY XML INDEX - EXTENDED INDEX - XML INDEX - COLUMNSTORE MAPPING INDEX (interno) - COLUMNSTORE DELETEBUFFER INDEX (interno) - COLUMNSTORE DELETEBITMAP INDEX (interno)
alloc_unit_type_desc	nvarchar(60)	Descrizione del tipo dell'unità di allocazione: - IN_ROW_DATA - LOB_DATA - ROW_OVERFLOW_DATA L'unità LOB_DATA di allocazione contiene i dati archiviati in colonne di tipo text, ntext, image, varchar(max), nvarchar(max), varbinary(max)e xml. Per ulteriori informazioni, vedi Tipi di dati. L'unità ROW_OVERFLOW_DATA di allocazione contiene i dati archiviati in colonne di tipo varchar(n), nvarchar(n), varbinary(n)e sql_variant di cui viene eseguito il push fuori riga.
index_depth	tinyint	Numero di livelli dell'indice. 1 = Heap o LOB_DATA ROW_OVERFLOW_DATA unità di allocazione.
index_level	tinyint	Livello corrente dell'indice. 0 per i livelli foglia dell'indice, gli heap e LOB_DATA le ROW_OVERFLOW_DATA unità di allocazione. Maggiore di per i livelli di 0 indice non foglia. index_level è la più alta a livello radice di un indice. I livelli nonaf degli indici vengono elaborati solo quando la modalità è DETAILED.
avg_fragmentation_in_percent	float	Frammentazione logica per gli indici o frammentazione extent per gli heap nell'unità IN_ROW_DATA di allocazione. Il valore viene misurato come percentuale e a tal fine vengono presi in considerazione più file. Per le definizioni della frammentazione logica ed extent, vedere Osservazioni. 0 per LOB_DATA le unità di allocazione e ROW_OVERFLOW_DATA . NULL per gli heap quando la modalità è SAMPLED.
fragment_count	bigint	Numero di frammenti nel livello foglia di un'unità di allocazione IN_ROW_DATA . Per altre informazioni sui frammenti, vedere Osservazioni. NULL per i livelli nonaf di un indice e LOB_DATA o ROW_OVERFLOW_DATA unità di allocazione. NULL per gli heap quando la modalità è SAMPLED.
avg_fragment_size_in_pages	float	Numero medio di pagine in un frammento nel livello foglia di un'unità IN_ROW_DATA di allocazione. NULL per i livelli nonaf di un indice e LOB_DATA o ROW_OVERFLOW_DATA unità di allocazione. NULL per gli heap quando la modalità è SAMPLED.
page_count	bigint	Numero totale di pagine di dati o di indice. Per un indice, il numero totale di pagine di indice nel livello corrente dell'albero B nell'unità IN_ROW_DATA di allocazione. Per un heap, il numero totale di pagine di dati nell'unità IN_ROW_DATA di allocazione. Per LOB_DATA le unità di allocazione o ROW_OVERFLOW_DATA il numero totale di pagine nell'unità di allocazione.
avg_page_space_used_in_percent	float	Percentuale media dello spazio di archiviazione dei dati utilizzato in tutte le pagine. Per un indice, la media si applica al livello corrente dell'albero B nell'unità IN_ROW_DATA di allocazione. Per un heap, la media di tutte le pagine di dati nell'unità IN_ROW_DATA di allocazione. Per LOB_DATA le unità di allocazione o ROW_OVERFLOW_DATA , la media di tutte le pagine nell'unità di allocazione. NULL quando la modalità è LIMITED.
record_count	bigint	Numero totale di record. Per un indice, il numero totale di record si applica al livello corrente dell'albero B nell'unità IN_ROW_DATA di allocazione. Per un heap, il numero totale di record nell'unità IN_ROW_DATA di allocazione. Nota: per un heap, il numero di record restituiti da questa funzione potrebbe non corrispondere al numero di righe restituite eseguendo un SELECT COUNT(*) oggetto sull'heap. Ciò è dovuto al fatto che una riga può contenere più record. In alcune situazioni di aggiornamento, ad esempio, una singola riga dell'heap potrebbe avere un record di inoltro e un record inoltrato in seguito all'operazione di aggiornamento. Inoltre, la maggior parte delle righe line-of-business di grandi dimensioni è suddivisa in più record nell'archiviazione LOB_DATA . Per LOB_DATA le unità di allocazione o ROW_OVERFLOW_DATA , il numero totale di record nell'unità di allocazione completa. NULL quando la modalità è LIMITED.
ghost_record_count	bigint	Numero di record fantasma pronti per la rimozione tramite l'attività di pulizia dei record fantasma nell'unità di allocazione. 0 per i livelli non foglia di un indice nell'unità IN_ROW_DATA di allocazione. NULL quando la modalità è LIMITED.
version_ghost_record_count	bigint	Numero di record fantasma mantenuti da una transazione di isolamento dello snapshot in attesa in un'unità di allocazione. 0 per i livelli non foglia di un indice nell'unità IN_ROW_DATA di allocazione. NULL quando la modalità è LIMITED.
min_record_size_in_bytes	int	Dimensioni minime dei record in byte. Per un indice, le dimensioni minime dei record si applicano al livello corrente dell'albero B nell'unità IN_ROW_DATA di allocazione. Per un heap, la dimensione minima del record nell'unità IN_ROW_DATA di allocazione. Per LOB_DATA le unità di allocazione o ROW_OVERFLOW_DATA , le dimensioni minime del record nell'unità di allocazione completa. NULL quando la modalità è LIMITED.
max_record_size_in_bytes	int	Dimensioni massime dei record in byte. Per un indice, la dimensione massima del record si applica al livello corrente dell'albero B nell'unità IN_ROW_DATA di allocazione. Per un heap, la dimensione massima del record nell'unità IN_ROW_DATA di allocazione. Per LOB_DATA le unità di allocazione o ROW_OVERFLOW_DATA , la dimensione massima del record nell'unità di allocazione completa. NULL quando la modalità è LIMITED.
avg_record_size_in_bytes	float	Dimensioni medie dei record in byte. Per un indice, la dimensione media dei record si applica al livello corrente dell'albero B nell'unità IN_ROW_DATA di allocazione. Per un heap, la dimensione media dei record nell'unità IN_ROW_DATA di allocazione. Per LOB_DATA le unità di allocazione o ROW_OVERFLOW_DATA , le dimensioni medie dei record nell'unità di allocazione completa. NULL quando la modalità è LIMITED.
forwarded_record_count	bigint	Numero di record in un heap che hanno inoltrato puntatori a un altro percorso dei dati. Questo stato si verifica durante un aggiornamento, quando non c'è spazio sufficiente per archiviare la nuova riga nella posizione originale. NULL per qualsiasi unità di allocazione diversa dalle unità di IN_ROW_DATA allocazione per un heap. NULL per gli heap quando la modalità è LIMITED.
compressed_page_count	bigint	Numero di pagine compresse. Per gli heap, le pagine appena allocate non PAGE vengono compresse. Un heap viene PAGE compresso in due condizioni speciali: quando i dati vengono importati in blocco o quando viene ricompilato un heap. Le operazioni DML tipiche che causano allocazioni di pagine non PAGE vengono compresse. Ricompilare un heap quando il compressed_page_count valore aumenta di dimensioni superiori alla soglia desiderata. Per le tabelle con un indice cluster, il compressed_page_count valore indica l'efficacia della PAGE compressione.
hobt_id	bigint	Heap o ID albero B dell'indice o della partizione. Per gli indici columnstore, si tratta dell'ID per un set di righe che tiene traccia dei dati columnstore interni per una partizione. I set di righe vengono archiviati come heap di dati o alberi B. Hanno lo stesso ID di indice dell'indice columnstore padre. Per altre informazioni, vedere sys.internal_partitions.
columnstore_delete_buffer_state	tinyint	0 = NOT_APPLICABLE 1 = OPEN 2 = DRAINING 3 = FLUSHING 4 = RETIRING 5 = READY Si applica a: SQL Server 2016 (13.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure
columnstore_delete_buffer_state_desc	nvarchar(60)	NOT VALID - l'indice padre non è un indice columnstore. OPEN - i deleter e gli scanner usano questa opzione. DRAINING - gli eliminatori stanno svuotando, ma gli scanner lo usano ancora. FLUSHING - il buffer viene chiuso e le righe nel buffer vengono scritte nella bitmap di eliminazione. RETIRING - Le righe nel buffer di eliminazione chiuso sono state scritte nella bitmap di eliminazione, ma il buffer non è stato troncato perché gli scanner lo usano ancora. I nuovi scanner non devono usare il buffer di ritiro perché il buffer aperto è sufficiente. READY - Questo buffer di eliminazione è pronto per l'uso. Si applica a: SQL Server 2016 (13.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure
version_record_count	bigint	Questo è il conteggio dei record di versione delle righe mantenuti in questo indice. Queste versioni di riga vengono gestite dalla funzionalità di ripristino accelerato del database. Si applica a: SQL Server 2019 (15.x) e versioni successive e database SQL di Azure
inrow_version_record_count	bigint	Numero di record di versione di ADR mantenuti nella riga di dati per il recupero rapido. Si applica a: SQL Server 2019 (15.x) e versioni successive e database SQL di Azure
inrow_diff_version_record_count	bigint	Numero di record di versione DIR mantenuti sotto forma di differenze rispetto alla versione di base. Si applica a: SQL Server 2019 (15.x) e versioni successive e database SQL di Azure
total_inrow_version_payload_size_in_bytes	bigint	Dimensione totale in byte dei record delle versioni in riga per questo indice. Si applica a: SQL Server 2019 (15.x) e versioni successive e database SQL di Azure
offrow_regular_version_record_count	bigint	Numero di record di versione mantenuti all'esterno della riga di dati originale. Si applica a: SQL Server 2019 (15.x) e versioni successive e database SQL di Azure
offrow_long_term_version_record_count	bigint	Numero di record di versione considerati a lungo termine. Si applica a: SQL Server 2019 (15.x) e versioni successive e database SQL di Azure

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:

La funzione a gestione dinamica sys.dm_db_index_physical_statssostituisce l'istruzione DBCC SHOWCONTIG.

Modalità di analisi

La modalità di esecuzione della funzione determina il livello di analisi eseguito per ottenere i dati statistici utilizzati dalla funzione. mode viene specificato come LIMITED, SAMPLEDo DETAILED. La funzione consente di attraversare le catene di pagine per le unità di allocazione che costituiscono le partizioni specificate della tabella o dell'indice. sys.dm_db_index_physical_stats richiede solo un blocco di tabella Intent-Shared (IS), indipendentemente dalla modalità in cui viene eseguita.

La LIMITED modalità è la modalità più veloce e analizza il numero minimo di pagine. Per un indice, viene eseguita l'analisi delle sole pagine di livello padre dell'albero B (ovvero le pagine sopra il livello foglia). Per un heap, vengono esaminate le pagine PFS e IAM associate e le pagine di dati di un heap vengono analizzate in LIMITED modalità .

Con LIMITED la modalità , compressed_page_count il NULL motore di database analizza solo le pagine non foglia dell'albero B e le pagine IAM e PFS dell'heap. Usare SAMPLED la modalità per ottenere un valore stimato per compressed_page_counte usare DETAILED la modalità per ottenere il valore effettivo per compressed_page_count. La SAMPLED modalità restituisce statistiche in base a un campione del 1% di tutte le pagine nell'indice o nell'heap. I risultati in SAMPLED modalità devono essere considerati approssimativi. Se l'indice o l'heap ha meno di 10.000 pagine, DETAILED viene usata la modalità anziché SAMPLED.

La DETAILED modalità analizza tutte le pagine e restituisce tutte le statistiche.

Le modalità sono progressivamente più lente da LIMITED a DETAILED, perché più lavoro viene eseguito in ogni modalità. Per misurare rapidamente le dimensioni o il livello di frammentazione di una tabella o di un indice, usare la LIMITED modalità . È il più veloce e non restituisce una riga per ogni livello non foglia nell'unità IN_ROW_DATA di allocazione dell'indice.

Usare le 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 . Tuttavia, il passaggio di valori non validi a queste funzioni può causare risultati imprevisti. Ad esempio, se non è possibile trovare il nome del database o dell'oggetto perché non esistono o non sono stati digitati correttamente, entrambe le funzioni restituiscono NULL. La sys.dm_db_index_physical_stats funzione interpreta NULL come valore jolly che specifica tutti i database o tutti gli oggetti.

Inoltre, la funzione viene elaborata prima che OBJECT_ID venga chiamata la sys.dm_db_index_physical_stats funzione e quindi viene valutata nel contesto del database corrente, non nel database specificato in database_id. Questo comportamento può causare la restituzione di un NULL valore da parte della OBJECT_ID funzione oppure, se il nome dell'oggetto esiste sia nel contesto di database corrente che nel database specificato, viene restituito un messaggio di errore. Negli esempi seguenti vengono illustrati questi risultati imprevisti.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database.
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2022;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

Procedura consigliata

Assicurarsi sempre che venga restituito un ID valido quando si usa DB_ID o OBJECT_ID. Ad esempio, quando si usa OBJECT_ID, specificare un nome in tre parti, ad OBJECT_ID(N'AdventureWorks2022.Person.Address')esempio , o testare il valore restituito dalle funzioni prima di usarle nella sys.dm_db_index_physical_stats funzione . Negli esempi A e B seguenti viene illustrato come specificare ID database e oggetto in modo sicuro.

Rilevare la frammentazione

La frammentazione avviene tramite il processo di modifica dei dati (INSERT, UPDATEe DELETE istruzioni) eseguite sulla tabella e, di conseguenza, agli indici definiti nella tabella. Poiché queste modifiche non vengono normalmente distribuite equamente tra le righe della tabella e degli indici, l'interezza di ogni pagina può variare nel tempo. Per le query che analizzano parte o tutti gli indici di una tabella, questo tipo di frammentazione può causare più letture di pagina, che ostacolano l'analisi parallela dei dati.

Il livello di frammentazione di un indice o di un heap viene visualizzato nella avg_fragmentation_in_percent colonna . Per gli heap, il valore rappresenta la frammentazione extent dell'heap. Per gli indici, il valore rappresenta la frammentazione logica dell'indice. A differenza di DBCC SHOWCONTIG, gli algoritmi di calcolo della frammentazione in entrambi i casi considerano l'archiviazione che si estende su più file e, pertanto, sono accurati.

Frammentazione logica

Percentuale delle pagine non ordinate nelle pagine foglia di un indice. Una pagina non ordinata è una pagina per la quale la pagina fisica successiva allocata all'indice non è la pagina a cui punta il puntatore di pagina successiva nella pagina foglia corrente.

Frammentazione dell'extent

Percentuale di extent non ordinati nelle pagine foglia di un heap. Un extent non ordinato è uno per il quale l'extent che contiene la pagina corrente per un heap non è fisicamente l'extent successivo dopo l'extent che contiene la pagina precedente.

Il valore di avg_fragmentation_in_percent deve essere il più vicino possibile a zero per ottenere prestazioni massime. Tuttavia, i valori compresi tra 0% e 10% possono essere accettabili. Tutti i metodi di riduzione della frammentazione, ad esempio la ricompilazione, la riorganizzazione o la ricreazione, possono essere usati per ridurre questi valori. Per altre informazioni su come analizzare il grado di frammentazione in un indice, vedere Ottimizzare la manutenzione degli indici per migliorare le prestazioni delle query e ridurre l'utilizzo delle risorse.

Ridurre la frammentazione in un indice

Quando la frammentazione di un indice avviene in modo tale da influire sulle prestazioni delle query, è possibile effettuare una delle tre operazioni seguenti:

• Eliminare e ricreare l'indice cluster.

  La ricreazione di un indice cluster ridistribuisce i dati e restituisce pagine di dati complete. Il livello di completezza può essere configurato usando l'opzione FILLFACTOR in CREATE INDEX. Gli svantaggi di questo metodo sono che l'indice è offline durante il ciclo di rilascio e ricreazione e che l'operazione è atomica. Se la creazione dell'indice viene interrotta, l'indice non viene ricreato. Per altre informazioni, vedere CREATE INDEX.

• Usare ALTER INDEX REORGANIZE, la sostituzione di DBCC INDEXDEFRAG, per riordinare le pagine a livello foglia dell'indice in un ordine logico. Poiché si tratta di un'operazione online, l'indice è disponibile durante l'esecuzione dell'istruzione. L'operazione può essere interrotta senza perdere il lavoro già completato. Lo svantaggio di questo metodo è che non funziona come un buon lavoro di riorganizzazione dei dati come operazione di ricompilazione dell'indice e non aggiorna le statistiche.

• Usare ALTER INDEX REBUILD, la sostituzione di DBCC DBREINDEX, per ricompilare l'indice online o offline. Per altre informazioni, vedere ALTER INDEX (Transact-SQL).

La frammentazione da sola non è un motivo sufficiente per riorganizzare o ricompilare un indice. L'effetto principale della frammentazione è che rallenta la velocità read-ahead effettiva delle pagine durante le analisi di indici, provocando tempi di risposta più lenti. Se il carico di lavoro della query in una tabella o un indice frammentato non comporta analisi, perché il carico di lavoro è principalmente ricerche singleton, la rimozione della frammentazione non può avere alcun effetto.

Nota

L'esecuzione DBCC SHRINKFILE o DBCC SHRINKDATABASE può introdurre frammentazione se un indice è parzialmente o completamente spostato durante l'operazione di compattazione. Pertanto, se è necessario eseguire un'operazione di compattazione, è consigliabile eseguirla prima della rimozione della frammentazione.

Ridurre la frammentazione in un heap

Per ridurre la frammentazione extent di un heap, creare un indice cluster nella tabella e quindi rimuovere l'indice. I dati vengono quindi ridistribuiti in maniera ottimale durante la creazione dell'indice cluster, tenendo in considerazione la distribuzione dello spazio disponibile nel database. Quando l'indice cluster viene quindi eliminato per ricreare l'heap, i dati non vengono spostati e rimangono in posizione ottimale. Per informazioni su come eseguire queste operazioni, vedere CREATE INDEX e DROP INDEX.

Attenzione

La creazione e l'eliminazione di un indice cluster in una tabella ricompila tutti gli indici non cluster in tale tabella due volte.

Compattare i dati degli oggetti di grandi dimensioni

Per impostazione predefinita, l'istruzione ALTER INDEX REORGANIZE compatta le pagine contenenti dati LOB (Large Object). Poiché le pagine LOB non vengono deallocate quando sono vuote, la compattazione di questi dati può migliorare l'uso dello spazio su disco se vengono eliminati molti dati LOB o viene eliminata una colonna LOB.

La riorganizzazione di un indice cluster specificato compatta tutte le colonne LOB contenute nell'indice cluster. La riorganizzazione di un indice non cluster compatta tutte le colonne LOB che sono colonne non chiave (incluse) nell'indice. Quando ALL viene specificato nell'istruzione , tutti gli indici associati alla tabella o alla vista specificata vengono riorganizzati. Inoltre, tutte le colonne LOB associate all'indice cluster, alla tabella sottostante o all'indice non cluster con colonne incluse vengono compattate.

Valutare l'uso dello spazio su disco

La colonna indica l'interezza avg_page_space_used_in_percent della pagina. Per ottenere un utilizzo ottimale dello spazio su disco, questo valore deve essere vicino al 100% per un indice che non dispone di molti inserimenti casuali. Tuttavia, un indice con molti inserimenti casuali e con pagine molto complete ha un numero maggiore di divisioni di pagina. e di conseguenza una maggiore frammentazione. Pertanto, per ridurre il numero di divisioni di pagina, è necessario che il valore sia inferiore a 100%. La ricompilazione di un indice con l'opzione FILLFACTOR specificata consente di modificare l'interezza della pagina per adattarsi al modello di query nell'indice. Per altre informazioni sul fattore di riempimento, vedere Specificare il fattore di riempimento per un indice. Inoltre, ALTER INDEX REORGANIZE compatta un indice cercando di riempire le pagine nell'oggetto specificato per l'ultima FILLFACTOR volta. facendo aumentare il valore in avg_space_used_in_percent. ALTER INDEX REORGANIZE non può ridurre l'interezza delle pagine. È necessario invece eseguire una ricompilazione dell'indice.

Valutare i frammenti di indice

Un frammento è composto da pagine foglia fisicamente consecutive nello stesso file per un'unità di allocazione. Ogni indice ha almeno un frammento. Il numero massimo di frammenti che un indice può avere è uguale al numero di pagine nel livello foglia dell'indice. La presenza di frammenti più grandi indica che è necessaria una quantità minore di I/O su disco per leggere lo stesso numero di pagine. Pertanto, maggiore è il avg_fragment_size_in_pages valore, migliore è il rendimento dell'analisi dell'intervallo. I avg_fragment_size_in_pages valori e avg_fragmentation_in_percent sono inversamente proporzionali l'uno all'altro. La ricompilazione o la riorganizzazione di un indice dovrebbe quindi ridurre la quantità di frammentazione e far aumentare le dimensioni del frammento.

Limiti

Non restituisce dati per gli indici columnstore cluster.

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 STATEo VIEW 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 CONTROL autorizzazioni negate per oggetti specifici.

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

Per altre informazioni, vedere Viste a gestione dinamica del sistema.

Esempi

Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.

R. Restituire informazioni su una tabella specificata

Nell'esempio seguente vengono restituite le statistiche su dimensioni e frammentazione per tutti gli indici e le partizioni della tabella Person.Address. La modalità di analisi viene impostata su LIMITED per ottimizzare le prestazioni e limitare le statistiche restituite. L'esecuzione di questa query richiede almeno l'autorizzazione CONTROL per la Person.Address tabella.

DECLARE @db_id SMALLINT;
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_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. Restituire informazioni su un heap

Nell'esempio seguente vengono restituite le statistiche per l'heap dbo.DatabaseLog nel database AdventureWorks2022. Poiché la tabella contiene dati LOB, viene restituita una riga per l'unità di allocazione LOB_DATA in aggiunta alla riga restituita per IN_ROW_ALLOCATION_UNIT che archivia le pagine di dati dell'heap. L'esecuzione di questa query richiede almeno l'autorizzazione CONTROL per la dbo.DatabaseLog tabella.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.DatabaseLog');
IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

C. Restituire informazioni per tutti i database

Nell'esempio seguente vengono restituite tutte le statistiche per tutte le tabelle e gli indici all'interno dell'istanza di SQL Server specificando il carattere jolly NULL per tutti i parametri. L'esecuzione di questa query richiede l'autorizzazione VIEW SERVER STATE .

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

D. Usare sys.dm_db_index_physical_stats in uno script per ricompilare o riorganizzare gli indici

Nell'esempio seguente vengono riorganizzate o ricompilate automaticamente tutte le partizioni di un database che hanno una frammentazione media superiore al 10%. L'esecuzione di questa query richiede l'autorizzazione VIEW DATABASE STATE . Nell'esempio viene specificato DB_ID come primo parametro senza includere un nome di database.

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
    AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;

    FETCH NEXT
    FROM partitions
    INTO @objectid,
        @indexid,
        @partitionnum,
        @frag;

    IF @@FETCH_STATUS < 0
        BREAK;

    SELECT @objectname = QUOTENAME(o.name),
        @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid
        AND index_id = @indexid;

    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid
        AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

E. Usare sys.dm_db_index_physical_stats per visualizzare il numero di pagine compresse

Nell'esempio seguente viene illustrato come visualizzare e confrontare il numero complessivo di pagine rispetto a quelle a cui è stata applicata una compressione di riga e di pagina. Tali informazioni possono essere utilizzate per determinare i vantaggi offerti dalla compressione per un indice o per una tabella.

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count,
    ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count,
    ips.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

F. Usare sys.dm_db_index_physical_stats in modalità SAMPLED

Nell'esempio seguente viene illustrato come SAMPLED la modalità restituisce un valore approssimativo diverso dai risultati della DETAILED modalità.

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

DECLARE @idx INT = 0;

WHILE @idx < 1000000
BEGIN
    INSERT INTO t3 (col1, col2)
    VALUES (
        @idx,
        REPLICATE('a', 100) + CAST(@idx AS VARCHAR(10)) + REPLICATE('a', 380)
        )

    SET @idx = @idx + 1
END

COMMIT;
GO

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'SAMPLED');

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'DETAILED');

G. Eseguire query sulle code di Service Broker per la frammentazione dell'indice

Si applica a: SQL Server 2016 (13.x) e versioni successive

Nell'esempio seguente viene illustrato come eseguire query sulle code di Broker del server per la frammentazione.

--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);

--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

Contenuto correlato

• Viste a gestione dinamica (DMV) di sistema
• Funzioni e viste a gestione dinamica correlate all'indice (Transact-SQL)
• sys.dm_db_index_operational_stats (Transact-SQL)
• sys.dm_db_index_usage_stats (Transact-SQL)
• sys.dm_db_partition_stats (Transact-SQL)
• sys.allocation_units (Transact-SQL)
• Guida di riferimento a Transact-SQL (Motore di database)