sys.dm_db_index_physical_stats (Transact-SQL)

La modalità di esecuzione della funzione determina il livello di analisi eseguito per ottenere i dati statistici utilizzati dalla funzione. mode è specificato come LIMITED, SAMPLED o 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. Per l'oggetto sys.dm_db_index_physical_stats viene richiesto solo un blocco a livello di tabella preventivo condiviso (IS), indipendentemente dalla modalità in cui viene eseguito. Per ulteriori informazioni sui blocchi, vedere Modalità blocco.

La modalità LIMITED è la più veloce ed esegue l'analisi del minor numero di pagine. Per un indice, viene eseguita l'analisi delle sole pagine di livello padre dell'albero b-tree (ovvero le pagine sopra il livello foglia). Per un heap, vengono esaminate solo le pagine associate PFS e IAM, mentre le pagine di dati non vengono analizzate. In SQL Server 2005, per tutte le pagine di un heap viene eseguita l'analisi in modalità LIMITED.

Con la modalità LIMITED, compressed_page_count è NULL perché il Motore di database consente di analizzare solo le pagine non foglia dell'albero b-tree e le pagine IAM e PFS dell'heap. Utilizzare la modalità SAMPLED per ottenere un valore stimato di compressed_page_count e la modalità DETAILED per ottenere il valore effettivo di compressed_page_count. Tramite la modalità SAMPLED vengono restituite le statistiche in base a un campione dell'1% di tutte le pagine nell'indice o nell'heap. I risultati della modalità SAMPLED devono essere considerati approssimativi. Se nell'indice o nell'heap sono presenti meno di 10.000 pagine, viene utilizzata la modalità DETAILED invece della modalità SAMPLED.

La modalità DETAILED esegue l'analisi di tutte le pagine e restituisce tutte le statistiche.

La velocità della modalità è decrescente da LIMITED a DETAILED, a causa dell'aumento della quantità di lavoro. Per misurare rapidamente le dimensioni o il livello di frammentazione di una tabella o di un indice, utilizzare la modalità LIMITED. Si tratta della modalità più veloce e non restituisce una riga per ogni livello non foglia nell'unità di allocazione IN_ROW_DATA dell'indice.

È possibile utilizzare le funzioni DB_ID e OBJECT_ID di Transact-SQL 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. Se, ad esempio, non è possibile trovare il nome del database o dell'oggetto perché inesistente o perché l'ortografia non è corretta, verrà restituito NULL da entrambe le funzioni. Il valore NULL viene interpretato dalla funzione sys.dm_db_index_physical_stats come valore di carattere jolly che specifica tutti i database o tutti gli oggetti.

La funzione OBJECT_ID viene inoltre elaborata prima della chiamata della funzione sys.dm_db_index_physical_stats e pertanto viene valutata nel contesto del database corrente, anziché del database specificato in database_id. Questo comportamento può causare la restituzione di un valore NULL da parte della funzione OBJECT_ID. In alternativa, se il nome dell'oggetto esiste sia nel contesto del database corrente, sia nel database specificato, può essere restituito un messaggio di errore. Negli esempi seguenti vengono illustrati questi risultati imprevisti.

Nell'esempio seguente OBJECT_ID viene valutato nel contesto del database master. Poiché Person.Address non esiste in master, la funzione restituisce NULL. Se viene specificato NULL come object_id, vengono restituiti tutti gli oggetti del database. Gli stessi risultati vengono restituiti quando viene specificato un oggetto non valido.

USE master;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO

Nell'esempio seguente vengono illustrati i risultati della specifica di un nome di oggetto valido che esiste sia nel contesto del database corrente che in quello del database specificato nel parametro database_id della funzione sys.dm_db_index_physical_stats. Viene restituito un errore perché il valore ID restituito da OBJECT_ID non corrisponde al valore ID dell'oggetto nel database specificato.

CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2008R2;
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

La frammentazione si verifica nel corso delle modifiche dei dati (istruzioni INSERT, UPDATE e DELETE) apportate alla tabella e, di conseguenza, agli indici in essa definiti. Poiché tali modifiche in genere non sono distribuite in modo equo tra le righe della tabella e gli indici, il livello di riempimento di ogni pagina può variare nel tempo. Nel caso di query che eseguono l'analisi di una parte o di tutti gli indici di una tabella, questo tipo di frammentazione potrebbe comportare letture di pagine aggiuntive, operazione che ostacola l'analisi parallela dei dati.

L'algoritmo per il calcolo della frammentazione è più preciso in SQL Server 2008 rispetto a SQL Server 2000. Di conseguenza, verranno visualizzati valori di frammentazione superiori. Ad esempio, in SQL Server 2000, una tabella non è considerata frammentata se nello stesso extent sono presenti le pagine 11 e 13 ma non la pagina 12, tuttavia, poiché per accedere a queste due pagine sarebbero necessarie due operazioni fisiche di I/O, in SQL Server 2008 la tabella viene considerata frammentata.

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

Frammentazione logica

Percentuale delle pagine non ordinate nelle pagine foglia di un indice. Una pagina risulta non ordinata quando la pagina fisica successiva allocata all'indice è diversa da quella a cui fa riferimento il puntatore di pagina successiva nella pagina foglia corrente.

Frammentazione extent

Percentuale di extent non ordinati nelle pagine foglia di un heap. Un extent è considerato non ordinato quando l'extent in cui è contenuta la pagina corrente per un heap non è fisicamente l'extent successivo a quello in cui è contenuta la pagina precedente.

Il valore per avg_fragmentation_in_percent deve essere il più possibile prossimo a zero per ottenere le prestazioni massime. Tuttavia, i valori compresi tra 0% e 10% possono essere accettabili. Per ridurre questi valori, è possibile utilizzare tutti i metodi per ridurre la frammentazione, ad esempio ricompilazione, riorganizzazione o ricreazione. Per ulteriori informazioni su come analizzare il livello di frammentazione in un indice, vedere Riorganizzazione e ricompilazione degli indici.

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

• Rimuovere e ricreare l'indice cluster.

  Ricreando un indice cluster i dati vengono ridistribuiti e si ottengono pagine di dati complete. È possibile configurare il livello di riempimento tramite l'opzione FILLFACTOR dell'istruzione CREATE INDEX. Gli svantaggi di questo metodo consistono nel fatto che l'indice è offline durante il ciclo di rimozione e ricreazione e che l'operazione è atomica. Se la creazione dell'indice viene interrotta, l'indice non viene ricreato. Per ulteriori informazioni, vedere CREATE INDEX (Transact-SQL).

• Utilizzare ALTER INDEX REORGANIZE, l'alternativa a DBCC INDEXDEFRAG, per riordinare le pagine di 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 il processo di riorganizzazione dei dati non è tanto efficiente quanto un'operazione di ricompilazione dell'indice e non aggiorna le statistiche.

• Utilizzare ALTER INDEX REBUILD, l'alternativa a DBCC DBREINDEX, per ricompilare l'indice online o offline. Per ulteriori informazioni, vedere ALTER INDEX (Transact-SQL).

La sola frammentazione 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 in un indice frammentato non comporta analisi, poiché il carico di lavoro è rappresentato principalmente da ricerche singleton, la rimozione della frammentazione potrebbe non avere alcun effetto. Per ulteriori informazioni, vedere questo sito Web Microsoft.

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 successivamente rimosso per ricreare l'heap, i dati non vengono spostati e rimangono nella posizione ottimale. Per informazioni sull'esecuzione di queste operazioni, vedere CREATE INDEX e DROP INDEX.

Attenzione
Se in una tabella viene creato ed eliminato un indice cluster, tutti gli indici non cluster in tale tabella vengono ricompilati due volte.

Per impostazione predefinita, l'istruzione ALTER INDEX REORGANIZE consente di compattare le pagine contenenti dati LOB. Poiché le pagine LOB non vengono deallocate quando sono vuote, la compattazione di questi dati può migliorare l'utilizzo dello spazio su disco se sono stati cancellati molti dati LOB o se 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 non siano colonne non chiave (incluse) nell'indice. Quando viene specificato ALL nell'istruzione, tutti gli indici associati alla tabella o vista vengono riorganizzati. Inoltre, tutte le colonne LOB associate all'indice cluster, alla tabella sottostante o all'indice non cluster con colonne vengono compattati.

Nella colonna avg_page_space_used_in_percent viene indicato il livello di riempimento pagina. Per ottenere un utilizzo ottimale dello spazio su disco, questo valore deve essere prossimo al 100% per un indice in cui non verranno inclusi molti inserimenti casuali. Tuttavia, un indice con molti inserimenti casuali e pagine con un alto livello di riempimento avrà un maggior numero 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 la modifica del livello di riempimento pagina in modo che corrisponda al modello di query nell'indice. Per ulteriori informazioni sul fattore di riempimento, vedere Fattore di riempimento. Inoltre, ALTER INDEX REORGANIZE consentirà di compattare un indice cercando di riempire le pagine fino al fattore di riempimento FILLFACTOR specificato per ultimo. In questo modo, il valore in avg_space_used_in_percent aumenta. Si noti che tramite ALTER INDEX REORGANIZE non può essere ridotto il livello di riempimento pagina. È necessario invece eseguire una ricompilazione dell'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 sarà il valore di avg_fragment_size_in_pages e migliori saranno le prestazioni di analisi dell'intervallo. I valori avg_fragment_size_in_pages e avg_fragmentation_in_percent sono inversamente proporzionali. La ricompilazione o la riorganizzazione di un indice dovrebbe quindi ridurre la quantità di frammentazione e far aumentare le dimensioni del frammento.

Nell'esempio seguente vengono restituite le dimensioni e le statistiche di frammentazione per tutti gli indici e le partizione della tabella Person.Address nel database AdventureWorks2008R2. 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 nella tabella Person.Address.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.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

Nell'esempio seguente vengono restituite le statistiche per l'heap dbo.DatabaseLog nel database AdventureWorks2008R2. 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 tabella dbo.DatabaseLog.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.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

Nell'esempio seguente vengono restituite tutte le statistiche per tutte le tabelle e gli indici presenti nell'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

Nell'esempio seguente vengono riorganizzate o ricompilate automaticamente tutte le partizioni di un database che hanno una frammentazione media superiore al 10%. Per l'esecuzione di questa query, è necessario disporre dell'autorizzazione VIEW DATABASE STATE. Nell'esempio viene specificato DB_ID come primo parametro senza includere un nome di database. Se il database corrente ha un livello di compatibilità pari a 80 o inferiore, viene generato un errore. Per risolvere l'errore, sostituire DB_ID() con un nome di database valido. Per ulteriori informazioni sui livelli di compatibilità dei database, vedere sp_dbcmptlevel (Transact-SQL).

-- 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
        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

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
JOIN sys.objects o on o.object_id = ips.object_id
ORDER BY record_count DESC;

Nell'esempio seguente viene illustrato il modo in cui tramite la modalità SAMPLED vengono restituiti risultati approssimativi diversi rispetto a quelli della modalità DETAILED.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
GO
BEGIN TRAN
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');