DBCC SHOWCONTIG (Transact-SQL)

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

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

Si applica a: SQL Server 2008 (10.0.x) e versioni successive

Convenzioni di 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 per verificare la presenza di 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, usare la funzione OBJECT_ID.

index_name | index_id

Indice da verificare per 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, usare 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. Un'analisi rapida non legge le pagine foglia o livello di 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 per compatibilità con le versioni precedenti. Anche se ALL_LEVELS viene specificato, viene elaborato solo il livello foglia di indice o il livello di 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 del set di risultati.

Quando table_id e FAST vengono specificati, DBCC SHOWCONTIG restituisce un set di risultati con solo le colonne seguenti:

• Pagine sottoposte ad analisi
• Cambi di extent
• Densità di analisi [conteggio ottimale:conteggio effettivo]
• Frammentazione analisi extent
• Frammentazione analisi logica

Quando TABLERESULTS viene specificato, DBCC SHOWCONTIG restituisce le colonne seguenti e anche le nove colonne descritte nella tabella precedente.

Quando WITH TABLERESULTS e FAST vengono specificati, il set di risultati è uguale a quando WITH TABLERESULTS viene specificato, ad eccezione delle colonne seguenti con valori Null:

Osservazioni

L'istruzione DBCC SHOWCONTIG attraversa la catena di pagine a livello foglia dell'indice specificato quando viene specificato index_id . 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 usato solo per misurare la frammentazione, è consigliabile usare l'opzione WITH FAST per ottenere prestazioni ottimali. Un'analisi veloce non legge le pagine foglia o a livello di dati dell'indice. L'opzione WITH FAST non si applica a un heap.

Restrizioni

DBCC SHOWCONTIG non visualizza i dati con tipi di dati ntext, text e image . La mancata visualizzazione è dovuta al fatto gli indici di testo che archiviano dati di tipo text e image non esistono più.

Inoltre, DBCC SHOWCONTIG non supporta alcune nuove funzionalità. Ad esempio:

• Se la tabella o l'indice specificato è partizionata, DBCC SHOWCONTIG visualizza solo la prima partizione della tabella o dell'indice specificato.
• DBCC SHOWCONTIG non visualizza informazioni sull'archiviazione di overflow delle righe e altri nuovi tipi di dati all'esterno delle righe, ad esempio nvarchar(max), varchar(max), varbinary(max)e xml.
• Gli indici spaziali non sono supportati da DBCC SHOWCONTIG.

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

Frammentazione delle tabelle

DBCC SHOWCONTIG determina se la tabella è fortemente frammentata. La frammentazione si verifica in seguito ai processi di modifica dei dati della tabella (istruzioni INSERT, UPDATE e DELETE). Poiché queste modifiche non vengono normalmente distribuite equamente tra le righe della tabella, la completezza di ogni pagina può variare nel 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. Il livello di completezza può essere configurato usando l'opzione FILLFACTOR in 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.

  Usare ALTER INDEX...REORGANIZE per riordinare le pagine a livello foglia dell'indice in un ordine logico. 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. Lo svantaggio di questo metodo è che il metodo non funziona come un buon processo di riorganizzazione dei dati come un'operazione di eliminazione o ricreazione dell'indice cluster.

• Ricompilare l'indice.

  Usare ALTER INDEX con REBUILD per ricompilare l'indice. Per altre informazioni, vedere ALTER INDEX (Transact-SQL).

Il numero Media byte disponibili per pagina e Media densità pagina (completa) nel set di risultati indicano il livello di riempimento delle pagine dell'indice. Il numero medio di byte liberi per pagina deve essere basso e il numero medio di densità di pagina (pieno) deve essere elevato per un indice che non includerà molti inserimenti casuali. L'eliminazione e la ricreazione di un indice con l'opzione FILLFACTOR specificata possono migliorare le statistiche. Inoltre, ALTER INDEX con compattare REORGANIZE un indice, tenendo conto del relativo FILLFACTORe migliorerà le statistiche.

È 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

R. Visualizzare le informazioni sulla frammentazione per una tabella

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

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO

B. Usare OBJECT_ID per ottenere l'ID tabella e sys.indexes per ottenere l'ID indice

Nell'esempio seguente vengono usate 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 AdventureWorks2022.

USE AdventureWorks2022;
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. Visualizzare un set di risultati abbreviato per una tabella

Nell'esempio seguente viene restituito un set di risultati abbreviato per la tabella Productnel database AdventureWorks2022.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO

D. Visualizzare il set di risultati completo per ogni indice in ogni tabella di un database

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

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

E. Usare DBCC SHOWCONTIG e DBCC INDEXDEFRAG per deframmentare gli indici in 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

Vedi anche

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