DBCC SHOWCONTIG (Transact-SQL)

DBCC SHOWCONTIG no muestra los datos con los tipos de datos ntext, text e image Esto se debe a que ya no existen los índices de texto (Id. de índice 255 en SQL Server 2000) que almacenan datos de texto e imagen. Para obtener más información acerca del Id. de índice 255, vea sys.sysindexes (Transact-SQL).

Además, DBCC SHOWCONTIG no admite algunas características nuevas. Por ejemplo:

• Si la tabla o el índice especificados tienen particiones, DBCC SHOWCONTIG solo muestra la primera partición de la tabla o el índice especificados.

• DBCC SHOWCONTIG no muestra la información de almacenamiento de desbordamiento de fila y otros tipos de datos no consecutivos nuevos como nvarchar(max), varchar(max), varbinary(max) y xml.

• DBCC SHOWCONTIG no admite los índices espaciales.

Todas las características nuevas son totalmente compatibles con la vista de administración dinámica sys.dm_db_index_physical_stats (Transact-SQL).

DBCC SHOWCONTIG determina si la tabla está muy fragmentada. La fragmentación de las tablas es consecuencia de los procesos de modificación de los datos (instrucciones INSERT, UPDATE y DELETE) efectuados en las tablas. Como dichas modificaciones no suelen estar distribuidas de forma equilibrada entre todas las filas de la tabla, el llenado de cada página puede variar con el paso del tiempo. En las consultas que examinan la totalidad o parte de una tabla, esta fragmentación de tabla puede ocasionar lecturas de páginas adicionales. Esto afecta al examen paralelo de los datos.

Cuando un índice está muy fragmentado, existen dos opciones para reducir la fragmentación:

• Quite y vuelva a crear un índice clúster.

  La reconstrucción de un índice clúster reorganiza los datos y hace que las páginas de datos se llenen. El grado de llenado se puede configurar mediante la opción FILLFACTOR en CREATE INDEX. El inconveniente de este método es que el índice está sin conexión durante el proceso de eliminación y nueva creación, y que la operación es atómica. Si se interrumpe la creación del índice, éste no se vuelve a crear.

• Reordene las páginas de nivel hoja del índice en un orden lógico.

  Utilice ALTER INDEX…REORGANIZE para reordenar las páginas de nivel hoja del índice en un orden lógico. Dado que esta operación se realiza en línea, el índice está disponible mientras se ejecuta la instrucción. También es posible interrumpir la operación sin perder todo el trabajo. El inconveniente de este método es que no es una forma tan buena de reorganizar los datos como la operación de quitar y volver a crear el índice clúster.

• Vuelve a generar el índice.

  Para volver a generar el índice, utilice ALTER INDEX con REBUILD. Para obtener más información, vea ALTER INDEX (Transact-SQL).

Avg. Bytes free per page y Avg. Page density (full) del conjunto de resultados indican el llenado de las páginas de índice. Avg. Bytes free per page debe ser bajo y Avg. Page density (full) debe ser alto para un índice que no tenga muchas inserciones aleatorias. Quitar y volver a crear un índice con la opción FILLFACTOR especificada puede mejorar estas estadísticas. Además, ALTER INDEX con REORGANIZE compactará un índice, teniendo en cuenta FILLFACTOR, lo que mejorará las estadísticas.

El nivel de fragmentación de un índice puede determinarse de las siguientes formas:

• Mediante la comparación de los valores de Extent Switches y Extents Scanned.

  El valor de Extent Switches debe ser lo más parecido posible al de Extents Scanned. Esta relación se calcula como el valor de Scan Density. Dicho valor debe ser lo más alto posible y se puede aumentar mediante la reducción de la fragmentación del índice.

  Nota

  Este método no funciona si el índice abarca varios archivos.

• Mediante la comprensión de los valores de Logical Scan Fragmentation y Extent Scan Fragmentation.

  El valor de Logical Scan Fragmentation y, en menor medida, el valor de Extent Scan Fragmentation ofrecen la mejor indicación del nivel de fragmentación de una tabla. Ambos valores deberían tender a cero tanto como fuera posible, aunque puede ser aceptable un valor entre el 0 y el 10 por ciento.

  Nota

  El valor de Extent Scan Fragmentation es alto si el índice abarca varios archivos. Para reducir estos valores, debe reducir la fragmentación del índice.

En el siguiente ejemplo se muestra la información de fragmentación para la tabla Employee.

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

En el siguiente ejemplo se utiliza OBJECT_ID y la vista de catálogo sys.indexes para obtener el Id. de tabla y el Id. de índice para el índice AK_Product_Name de la tabla Production.Product en la base de datos 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

En el siguiente ejemplo se devuelve un conjunto de resultados resumido para la tabla Product de la base de datos AdventureWorks2008R2.

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

En el siguiente ejemplo se devuelve un conjunto de resultados de tabla completo para todos los índices de todas las tablas de la base de datos AdventureWorks2008R2 .

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

En el siguiente ejemplo se muestra una forma sencilla de desfragmentar todos los índices de una base de datos que está fragmentada por encima de un umbral declarado.

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