DBCC SHOWCONTIG (Transact-SQL)

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

Importante
Questa caratteristica verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Non utilizzare questa caratteristica in un nuovo progetto di sviluppo e modificare non appena possibile le applicazioni in cui è attualmente implementata. Utilizzare sys.dm_db_index_physical_stats in alternativa.

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 un'analisi rapida dell'indice con restituzione di informazioni di output minime. Durante un'analisi 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 ad analisi	Numero di pagine della tabella o dell'indice.
Extent sottoposti ad analisi	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 analisi [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 analisi logica	Percentuale di pagine non ordinate restituite dall'analisi 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 analisi extent	Percentuale di extent non ordinati rilevati durante l'analisi 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. 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 ad analisi. Maggiore è il numero, minore sarà 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 ad analisi

• Cambi di extent

• Densità di analisi [conteggio ottimale:conteggio effettivo]

• Frammentazione analisi extent

• Frammentazione analisi 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.
Pagine	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 (*) sull'heap. Questo perché una riga potrebbe contenere più record. Ad esempio, in alcune situazioni di aggiornamento, un'unica riga dell'heap potrebbe presentare un record di inoltro e un record inoltrato a seguito dell'operazione di aggiornamento. Inoltre, nell'archiviazione LOB_DATA la maggior parte delle righe LOB viene suddivisa in più record.
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	Numero medio di byte disponibili nelle pagine sottoposte ad analisi. Maggiore è il numero, minore sarà 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.
AveragePageDensity	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.
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	Percentuale di pagine non ordinate restituite dall'analisi 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.
ExtentFragmentation	Percentuale di extent non ordinati rilevati durante l'analisi 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. 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 l'analisi delle pagine di dati della tabella specificata. L'operazione richiede esclusivamente 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 ottenere dati di misurazione della frammentazione, è consigliabile utilizzare l'opzione WITH FAST per ottenere prestazioni ottimali. Durante un'analisi rapida non vengono lette le pagine del livello foglia o dati dell'indice. L'opzione WITH FAST non si applica a un heap.

L'algoritmo per il calcolo della frammentazione è più preciso in SQL Server 2008 e nelle versioni successive 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.

Restrizioni

DBCC SHOWCONTIG non visualizza dati di tipo ntext, text e image. La mancata visualizzazione è dovuta al fatto gli indici di testo (indici con ID 255 in SQL Server 2000) che archiviano dati di tipo text e image non esistono più. Per ulteriori informazioni sugli indici con ID 255, vedere sys.sysindexes (Transact-SQL).

Inoltre, DBCC SHOWCONTIG non supporta alcune nuove caratteristiche. 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.

• Gli indici spaziali non sono supportati da DBCC SHOWCONTIG.

Tutte le nuove caratteristiche sono supportate completamente dalla DMV sys.dm_db_index_physical_stats (Transact-SQL).

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 l'analisi di un'intera tabella o di una parte di tabella, tale frammentazione della tabella potrebbe comportare letture di pagine aggiuntive, operazione che ostacola l'analisi 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 offline 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 online 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.

• Ricompilare l'indice.

  Per ricompilare 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 riempimento delle pagine di indice. Il numero Media byte disponibili per pagina deve essere basso e il numero Media densità pagina (completa) deve essere alto per un indice che non avrà molti 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 di conseguenza 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 ad analisi.

  Il valore Cambi di extent deve essere il più possibile prossimo al valore Extent sottoposti ad analisi. Questo rapporto viene calcolato dal valore Densità di analisi. 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 analisi logica e Frammentazione analisi extent.

  Il valore Frammentazione analisi logica e, anche se in misura minore, il valore Frammentazione analisi 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 analisi 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 AdventureWorks2008R2;
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 AdventureWorks2008R2.

USE AdventureWorks2008R2;
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 AdventureWorks2008R2.

USE AdventureWorks2008R2;
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 AdventureWorks2008R2 .

USE AdventureWorks2008R2;
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

Riferimento

ALTER INDEX (Transact-SQL)

CREATE INDEX (Transact-SQL)

DBCC (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

OBJECT_ID (Transact-SQL)

sys.indexes (Transact-SQL)