DBCC SHOWCONTIG (Transact-SQL)

Articolo
12/15/2008

Data aggiornamento: 15 settembre 2007

Visualizza informazioni sulla frammentazione dei dati e degli indici per la tabella o vista specificata.

Importante:
Questa funzionalità verrà rimossa in una delle prossime versioni di Microsoft SQL Server. Evitare di utilizzare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Utilizzare sys.dm_db_index_physical_stats in alternativa.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

DBCC SHOWCONTIG 
[ ( 
    { table_name | table_id | view_name | view_id } 
    [ , index_name | index_id ] 
) ] 
    [ WITH 
        { 
         [ , [ ALL_INDEXES ] ] 
         [ , [ TABLERESULTS ] ] 
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ] 
         [ NO_INFOMSGS ]
         }
    ]

Argomenti

table_name | table_id | view_name | view_id
Tabella o vista di cui controllare le informazioni sulla frammentazione. Se viene omesso, vengono controllate tutte le tabelle e le viste indicizzate nel database corrente. Per ottenere l'ID della tabella o vista, utilizzare la funzione OBJECT_ID.

index_name | index_id
Indice di cui controllare le informazioni sulla frammentazione. Se viene omesso, l'istruzione elabora l'indice di base per la tabella o vista specificata. Per ottenere l'ID dell'indice, utilizzare la vista del catalogo sys.indexes.

WITH
Specifica le opzioni per il tipo di informazioni restituite dall'istruzione DBCC.

FAST
Specifica se eseguire una scansione rapida dell'indice con restituzione di informazioni di output minime. Durante una scansione rapida non vengono lette le pagine del livello foglia o dati dell'indice.

ALL_INDEXES
Visualizza i risultati per tutti gli indici delle tabelle e viste specificate, anche se viene indicato un indice specifico.

TABLERESULTS
Visualizza i risultati come set di righe, con informazioni aggiuntive.

ALL_LEVELS
Supportata solo per compatibilità con le versioni precedenti. Anche se si specifica ALL_LEVELS, viene elaborato solo il livello foglia dell'indice oppure il livello dati della tabella.

NO_INFOMSGS
Evita la visualizzazione di tutti i messaggi informativi con livello di gravità compreso tra 0 e 10.

Set di risultati

Nella tabella seguente vengono descritte le informazioni nel set di risultati.

Statistiche

Descrizione

Pagine sottoposte a scansione

Numero di pagine della tabella o dell'indice.

Extent sottoposti a scansione

Numero di extent della tabella o dell'indice.

Cambi di extent

Numero di passaggi dell'istruzione DBCC da un extent all'altro durante l'attraversamento delle pagine della tabella o dell'indice.

Media pagine per extent

Numero di pagine per extent nella catena di pagine.

Densità di scansione [conteggio ottimale:conteggio effettivo]

Valore percentuale. Rapporto tra Conteggio ottimale e Conteggio effettivo. Questo valore è 100 se tutti gli elementi sono contigui, è minore di 100 in presenza di frammentazioni.

Conteggio ottimale rappresenta il numero ideale di cambi di extent se tutti gli elementi fossero contigui. Conteggio effettivo rappresenta il numero effettivo di cambi di extent.

Frammentazione scansione logica

Percentuale di pagine non ordinate restituite dalla scansione delle pagine foglia di un indice. Questo valore non è rilevante per gli heap. 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 scansione extent

Percentuale di extent non ordinati rilevati durante la scansione delle pagine foglia di un indice. Questo valore non è rilevante per gli heap. Un extent risulta non ordinato quando l'extent contenente la pagina corrente di un indice non corrisponde fisicamente all'extent successivo a quello che contiene la pagina precedente di un indice.

ms175008.note(it-it,SQL.90).gif Nota:

Questo numero non è significativo se l'indice è esteso a più file.

Media byte disponibili per pagina

Numero medio di byte disponibili nelle pagine sottoposte a scansione. Maggiore è questo numero, minore è il livello di riempimento delle pagine. I numeri minori indicano una situazione migliore se nell'indice non verranno eseguiti numerosi inserimenti casuali. Anche le dimensioni delle righe influiscono su questo valore, che risulta maggiore per righe di grandi dimensioni.

Media densità pagina (completa)

Densità media della pagina, in percentuale. Questo valore tiene conto delle dimensioni delle righe e pertanto rappresenta un'indicazione più precisa dell'effettivo livello di riempimento delle pagine. Sono preferibili valori elevati.

Se si specificano table_id e l'opzione FAST, l'istruzione DBCC SHOWCONTIG restituisce un set di risultati che include solo le colonne seguenti.

Pagine sottoposte a scansione
Cambi di extent
Densità di scansione [conteggio ottimale:conteggio effettivo]
Frammentazione scansione extent
Frammentazione scansione logica

Se si specifica TABLERESULTS, DBCC SHOWCONTIG restituisce le colonne seguenti oltre alle nove colonne descritte nella tabella precedente.

Statistiche

Descrizione

Nome oggetto

Nome della tabella o vista elaborata.

ObjectId

ID del nome di oggetto.

IndexName

Nome dell'indice elaborato. NULL per un heap.

IndexId

ID dell'indice. 0 per un heap.

Level

Livello dell'indice. Il livello 0 corrisponde al livello foglia (o dati) dell'indice.

Il livello è 0 per un heap.

Pages

Numero di pagine che compongono tale livello dell'indice o l'intero heap.

Rows

Numero di record di dati o dell'indice a tale livello dell'indice. Nel caso di un heap, corrisponde al numero di record di dati dell'intero heap.

Per un heap, il numero di record restituito da questa funzione potrebbe non corrispondere al numero di righe restituito eseguendo un SELECT COUNT (*) rispetto all'heap. Questo succede perché una riga può contenere più record. Ad esempio, in alcune situazioni di aggiornamento, una singola riga heap può avere un record di inoltro e un record inoltrato come risultato dell'operazione di aggiornamento. Inoltre, la maggior parte delle righe LOB grandi è suddivisa in più record in archiviazione di LOB_DATA.

MinimumRecordSize

Dimensioni minime dei record in tale livello dell'indice o nell'intero heap.

MaximumRecordSize

Dimensioni massime dei record in tale livello dell'indice o nell'intero heap.

AverageRecordSize

Dimensioni medie dei record in tale livello dell'indice o nell'intero heap.

ForwardedRecords

Numero di record inoltrati in tale livello dell'indice o nell'intero heap.

Extents

Numero di extent in tale livello dell'indice o nell'intero heap.

ExtentSwitches

Numero di passaggi dell'istruzione DBCC da un extent all'altro durante l'attraversamento delle pagine della tabella o dell'indice.

AverageFreeBytes

AveragePageDensity

ScanDensity

Valore percentuale. Rapporto tra BestCount e ActualCount. Questo valore è 100 se tutti gli elementi sono contigui, è minore di 100 in presenza di frammentazioni.

BestCount

Rappresenta il numero ideale di cambi di extent se tutti gli elementi fossero contigui.

ActualCount

Rappresenta il numero effettivo di cambi di extent.

LogicalFragmentation

ExtentFragmentation

ms175008.note(it-it,SQL.90).gif Nota:

Questo numero non è significativo se l'indice è esteso a più file.

Se si specificano le opzioni WITH TABLERESULTS e FAST, il set di risultati è uguale a quello restituito specificando WITH TABLERESULTS, con l'eccezione delle colonne seguenti che avranno valori Null:

Rows	Extents
MinimumRecordSize	AverageFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
ForwardedRecords

Osservazioni

Quando si specifica index_id, l'istruzione DBCC SHOWCONTIG attraversa la catena di pagine al livello foglia dell'indice specificato. Se si specifica solo table_id oppure index_id è 0, viene eseguita la scansione delle pagine di dati della tabella specificata. L'operazione richiede solo un blocco a livello di tabella preventivo condiviso (IS). In questo modo è possibile eseguire tutti gli aggiornamenti e gli inserimenti, con l'eccezione delle operazioni che richiedono un blocco a livello di tabella esclusivo (X). Ciò consente di ottenere una velocità di esecuzione accettabile senza riduzione della concorrenza per il numero di statistiche restituite. Tuttavia, se il comando viene utilizzato esclusivamente per misurare la frammentazione, è consigliabile utilizzare l'opzione WITH FAST per avere prestazioni ottimali. Durante una scansione rapida non vengono lette le pagine del livello foglia o dati dell'indice. L'opzione WITH FAST non è applicabile agli heap.

Modifiche in SQL Server 2005

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

DBCC SHOWCONTIG non visualizza dati di tipo ntext, text e image perché gli indici di testo (indici con ID 255 in SQL Server 2000) che archiviano dati di tipo text e image non esistono più in SQL Server 2005. Per ulteriori informazioni sugli indici con ID 255, vedere sys.sysindexes (Transact-SQL).

Inoltre, DBCC SHOWCONTIG non supporta alcune nuove funzionalità di SQL Server 2005. Ad esempio:

Se la tabella o l'indice specificato è partizionato, DBCC SHOWCONTIG visualizza solo la prima partizione della tabella o dell'indice specificato.
DBCC SHOWCONTIG non supporta la visualizzazione di informazioni di archiviazione per i dati di overflow della riga e di altri tipi nuovi per dati all'esterno di righe, come nvarchar(max), varchar(max), varbinary(max) e xml.

Tutte le nuove funzionalità di SQL Server 2005 sono supportate completamente dalla vista a gestione dinamica sys.dm_db_index_physical_stats.

Frammentazione della tabella

L'istruzione DBCC SHOWCONTIG determina se la tabella è molto frammentata. La frammentazione si verifica in seguito ai processi di modifica dei dati della tabella (istruzioni INSERT, UPDATE e DELETE). Poiché tali modifiche in genere non sono distribuite in modo equo tra le righe della tabella, il livello di riempimento di ogni pagina può variare nel corso del tempo. Nel caso di query che eseguono la scansione di un'intera tabella o di una parte di tabella, tale frammentazione della tabella potrebbe comportare letture di pagine aggiuntive, operazione che ostacola la scansione parallela dei dati.

Per ridurre il livello di frammentazione di un indice molto frammentato, è possibile eseguire una delle operazioni seguenti:

Eliminare e ricreare un indice cluster.
Quando si ricrea un indice cluster, i dati vengono riorganizzati e si ottengono pagine di dati complete. È possibile configurare il livello di riempimento tramite l'opzione FILLFACTOR dell'istruzione CREATE INDEX. Questo metodo presenta due svantaggi, ovvero l'indice rimane non in linea durante l'operazione di eliminazione o ricostruzione e l'operazione è atomica. Se la creazione dell'indice viene interrotta, l'indice non viene ricreato.
Ridisporre in ordine logico le pagine del livello foglia dell'indice.
Per ridisporre in ordine logico le pagine del livello foglia dell'indice, utilizzare ALTER INDEX…REORGANIZE. L'operazione viene eseguita in linea e pertanto l'indice rimane disponibile durante l'esecuzione dell'istruzione. È inoltre possibile interrompere l'operazione senza perdere il lavoro completato. Con questo metodo, tuttavia, i dati non vengono riorganizzati in modo altrettanto efficiente di quanto consentito dall'operazione di eliminazione o ricostruzione di un indice cluster.
Ricostruire l'indice.
Per ricostruire l'indice, utilizzare ALTER INDEX con REBUILD. Per ulteriori informazioni, vedere ALTER INDEX (Transact-SQL).

Le statistiche Media byte disponibili per pagina e Media densità pagina (completa) nel set di risultati indicano il livello di riempimento delle pagine dell'indice. Il valore di Media byte disponibili per pagina deve essere basso e il valore di Media densità pagina (completa) deve essere elevato per un indice per cui non sono previsti numerosi inserimenti casuali. L'eliminazione e la ricostruzione di un indice con l'opzione FILLFACTOR può consentire di migliorare tali statistiche. L'istruzione ALTER INDEX con l'opzione REORGANIZE consente inoltre di compattare un indice tenendo conto del valore FILLFACTOR corrispondente, con un conseguente miglioramento delle statistiche.

[!NOTA] Per un indice con numerosi inserimenti casuali e pagine molto piene si verificherà un maggior numero di divisioni di pagina e ciò porta a una maggiore frammentazione.

È possibile determinare il livello di frammentazione di un indice nei modi seguenti:

Tramite il confronto dei valori di Cambi di extent ed Extent sottoposti a scansione.
Il valore Cambi di extent deve essere il più possibile prossimo al valore Extent sottoposti a scansione. Questo rapporto viene calcolato dal valore Densità di scansione. Tale valore deve essere il più alto possibile e può essere migliorato riducendo la frammentazione dell'indice.

[!NOTA] Questo metodo non funziona se l'indice è esteso a più file.
Tramite l'analisi dei valori Frammentazione scansione logica e Frammentazione scansione extent.
Il valore Frammentazione scansione logica e, anche se in misura minore, il valore Frammentazione scansione extent sono i migliori indicatori del livello di frammentazione di una tabella. Entrambi i valori dovrebbero essere il più possibile prossimi allo zero, anche se possono essere accettabili valori compresi tra 0% e 10%.

[!NOTA] Il valore Frammentazione scansione extent sarà elevato se l'indice è esteso a più file. Per ridurre questo valore, è necessario ridurre la frammentazione dell'indice.

Autorizzazioni

L'utente deve essere il proprietario della tabella oppure un membro del ruolo predefinito del server sysadmin o dei ruoli predefiniti del database db_owner o db_ddladmin.

Esempi

A. Visualizzazione delle informazioni sulla frammentazione di una tabella

Nell'esempio seguente vengono visualizzate informazioni sulla frammentazione della tabella Employee.

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("HumanResources.Employee");
GO

B. Utilizzo di OBJECT_ID per ottenere l'ID della tabella e di sys.indexes per ottenere l'ID dell'indice

Nell'esempio seguente vengono utilizzate la funzione OBJECT_ID e la vista del catalogo sys.indexes per ottenere l'ID di tabella e l'ID di indice per l'indice AK_Product_Name della tabella Production.Product nel database AdventureWorks .

USE AdventureWorks;
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('Production.Product')
SELECT @indid = index_id 
FROM sys.indexes
WHERE object_id = @id 
   AND name = 'AK_Product_Name'
DBCC SHOWCONTIG (@id, @indid);
GO

C. Visualizzazione di un set di risultati abbreviato per una tabella

Nell'esempio seguente viene restituito un set di risultati abbreviato per la tabella Product nel database AdventureWorks .

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("Production.Product", 1) WITH FAST;
GO

D. Visualizzazione del set di risultati completo per ogni indice di tutte le tabelle di un database

Nell'esempio seguente viene restituito un set completo di risultati relativi a ogni indice di tutte le tabelle del database AdventureWorks .

USE AdventureWorks;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO

E. Utilizzo di DBCC SHOWCONTIG e DBCC INDEXDEFRAG per deframmentare gli indici di un database

Nell'esempio seguente viene illustrato un metodo semplice per deframmentare tutti gli indici di un database il cui livello di frammentazione è superiore alla soglia massima specificata.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN;
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

Vedere anche

Cronologia modifiche

Versione	Cronologia
15 settembre 2007	Contenuto modificato: Correzione delle informazioni di blocco nella sezione relativa alle note. In SQL Server 2005, questo comando utilizza solo un blocco di tabella preventivo condiviso (IS), non un blocco S come precedentemente affermato. Aggiunta di un chiarimento sulla definizione della colonna Righe per gli heap.
17 luglio 2006	Nuovo contenuto: Aggiunta di informazioni relative all'algoritmo per il calcolo della frammentazione nella sezione "Modifiche in SQL Server 2005".
5 dicembre 2005	Contenuto modificato: Correzione della descrizione di LogicalFragmentation.