DBCC SHOWCONTIG (Transact-SQL)

Artículo
12/15/2008

Actualizado: 15 de septiembre de 2007

Muestra información sobre la fragmentación de los datos y los índices de la tabla o vista especificada.

Importante:
Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan. Utilice sys.dm_db_index_physical_stats en su lugar.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

table_name | table_id | view_name | view_id
Es la tabla o la vista cuya información de fragmentación se comprueba. Si no se especifica, se comprueban todas las tablas y vistas indizadas de la base de datos actual. Para obtener el identificador de la tabla o la vista, utilice la función OBJECT_ID.

index_name | index_id
Es el índice cuya información de fragmentación se comprueba. Si no se especifica, la instrucción procesa el índice base de la tabla o la vista especificada. Para obtener el identificador del índice, utilice la vista de catálogo sys.indexes.

WITH
Especifica las opciones del tipo de información que devuelve la instrucción DBCC.

FAST
Especifica si se realiza un examen rápido del índice y se ofrece la información de salida mínima. Un examen rápido no lee las páginas de nivel de datos u hoja del índice.

ALL_INDEXES
Muestra el resultado de todos los índices para las tablas y vistas especificadas, aunque se haya especificado un índice determinado.

TABLERESULTS
Muestra el resultado como un conjunto de filas, con información adicional.

ALL_LEVELS
Se mantiene únicamente por compatibilidad con versiones anteriores. Aunque se especifique ALL_LEVELS, sólo se procesa el nivel de hoja del índice o el nivel de datos de tabla.

NO_INFOMSGS
Suprime todos los mensajes informativos con niveles de gravedad entre 0 y 10.

Conjuntos de resultados

En la tabla siguiente se describe la información del conjunto de resultados.

Estadística

Descripción

Páginas examinadas

Número de páginas de la tabla o el índice.

Extensiones examinadas

Número de extensiones de la tabla o el índice.

Cambios de extensión

Número de veces que la instrucción DBCC se ha movido de una extensión a otra al recorrer las páginas de la tabla o el índice.

Promedio de páginas por extensión

Número de páginas por extensión en la cadena de páginas.

Densidad de examen [Mejor recuento: Recuento real]

Es un porcentaje. Es la relación entre Cuenta óptima y Cuenta real. Este valor es 100 si todo está situado de forma contigua; si dicho valor es inferior a 100, existe fragmentación.

Cuenta óptima es el número ideal de cambios de extensión si todo está vinculado de forma contigua. Cuenta real es el número real de cambios de extensión.

Fragmentación de examen lógico

Porcentaje de páginas no ordenadas resultante del examen de las páginas hoja de un índice. Este número no es pertinente para los montones. Una página no ordenada es aquella en la que la siguiente página física asignada al índice no es la que señala el puntero de página siguiente en la página hoja actual.

Fragmentación de examen de extensión

Porcentaje de extensiones no ordenadas resultante del examen de las páginas hoja de un índice. Este número no es pertinente para los montones. Una extensión no ordenada es aquella en que la extensión que contiene la página actual de un índice no es físicamente la extensión siguiente a la que contiene la página anterior de un índice.

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

Este número carece de significado si el índice abarca varios archivos.

Promedio de bytes por página

Valor promedio de los bytes libres de las páginas examinadas. Cuanto mayor es el número, menos llenas estarán las páginas. Los números menores funcionan mejor si el índice no contiene muchas inserciones aleatorias. Este número también está influido por el tamaño de la fila; un tamaño de fila grande puede provocar un número más alto.

Promedio de densidad de página (completa)

Promedio de densidad de página en porcentaje. Este valor tiene en cuenta el tamaño de la fila. Por consiguiente, dicho valor es una medida más precisa del grado de llenado de las páginas. Cuanto mayor sea el porcentaje, mejor.

Cuando se especifica table_id y FAST, DBCC SHOWCONTIG devuelve un conjunto de resultados con sólo las siguientes columnas.

Páginas examinadas
Cambios de extensión
Densidad de examen [Mejor recuento:Recuento real]
Fragmentación de examen de extensión
Fragmentación de examen lógico

Si se especifica TABLERESULTS, DBCC SHOWCONTIG devuelve las siguientes columnas además de las nueve descritas en la tabla anterior.

Estadística

Descripción

Nombre de objeto

Nombre de la tabla o la vista procesada.

ObjectId

Identificador del nombre del objeto.

IndexName

Nombre del índice procesado. Es NULL para un montón.

IndexId

Identificador del índice. Es 0 para un montón.

Level

Nivel del índice. El nivel 0 es el nivel de hoja o datos del índice.

Para un montón, Level es 0.

Pages

Número de páginas que componen el nivel del índice o de todo el montón.

Rows

Número de registros de datos o índices en este nivel del índice. Para un montón, este valor es el número de registros de datos en todo el montón.

Para un montón, el número de registros devuelto por esta función podría no coincidir con el número de filas devueltas al ejecutar una instrucción SELECT COUNT(*) contra dicho montón. Esto es así porque una fila puede contener varios registros. Por ejemplo, bajo algunas situaciones de actualización, una única fila del montón puede tener un registro de reenvío y un registro reenviado como resultado de la operación de actualización. Asimismo, las filas LOB más grandes se dividen en varios registros de almacenamiento de tipo LOB_DATA.

MinimumRecordSize

Tamaño mínimo del registro en el nivel del índice o en todo el montón.

MaximumRecordSize

Tamaño máximo del registro en el nivel del índice o en todo el montón.

AverageRecordSize

Promedio de tamaño del registro en el nivel del índice o en todo el montón.

ForwardedRecords

Número de registros reenviados en el nivel del índice o en todo el montón.

Extents

Número de extensiones en el nivel del índice o en todo el montón.

ExtentSwitches

Número de veces que la instrucción DBCC se ha movido de una extensión a otra al recorrer las páginas de la tabla o el índice.

AverageFreeBytes

AveragePageDensity

ScanDensity

Es un porcentaje. Es la relación entre BestCount y ActualCount. Este valor es 100 si todo está situado de forma contigua; si dicho valor es inferior a 100, existe fragmentación.

BestCount

Es el número idóneo de cambios de extensión si todo está vinculado de forma contigua.

ActualCount

Es el número real de cambios de extensión.

LogicalFragmentation

Porcentaje de páginas no ordenadas resultante del examen por las páginas hoja del índice. Este número no es pertinente para los montones. Una página no ordenada es aquella en la que la siguiente página física asignada al índice no es la que señala el puntero de página siguiente en la página hoja actual.

ExtentFragmentation

Porcentaje de extensiones no ordenadas resultante del examen de las páginas hoja del índice. Este número no es pertinente para los montones. Una extensión no ordenada es aquella en que la extensión que contiene la página actual de un índice no es físicamente la extensión siguiente a la que contiene la página anterior de un índice.

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

Este número carece de significado si el índice abarca varios archivos.

Cuando se especifican WITH TABLERESULTS y FAST, el conjunto de resultados es el mismo que cuando se especifica WITH TABLERESULTS, con la diferencia de que las siguientes columnas tendrán valores NULL:

Rows	Extents
MinimumRecordSize	AverageFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
ForwardedRecords

Notas

Cuando se especifica index_id, la instrucción DBCC SHOWCONTIG recorre la cadena de páginas en el nivel de hoja del índice especificado. Si sólo se especifica table_id o si index_id es 0, se examinan las páginas de datos de la tabla especificada. La operación sólo requiere un bloqueo de tabla con intención compartida (IS). De este modo, se pueden realizar todas las actualizaciones e inserciones excepto las que requieren un bloqueo de tabla exclusivo (X). Esto permite un equilibrio entre la velocidad de ejecución y la no reducción de la simultaneidad con respecto al número de estadísticas devueltas. No obstante, si el comando se va a utilizar sólo para medir la fragmentación, se recomienda utilizar la opción WITH FAST para que su rendimiento sea óptimo. Un examen rápido no lee las páginas de nivel de datos u hoja del índice. La opción WITH FAST no se aplica a un montón.

Cambios en SQL Server 2005

El algoritmo para calcular la fragmentación es más preciso en SQL Server 2005 que en SQL Server 2000. En consecuencia, los valores de fragmentación serán aparentemente mayores. Por ejemplo, en SQL Server 2000, una tabla no se considera fragmentada si tiene la página 11 y la página 13 en la misma extensión, pero no la página 12. Sin embargo, para tener acceso a estas dos páginas, se requerirían dos operaciones de E/S físicas, de modo que se tiene en cuenta como fragmentación en SQL Server 2005.

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

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

Si la tabla o el índice especificados tienen particiones, DBCC SHOWCONTIG sólo muestra la primera.
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.

Todas las características nuevas de SQL Server 2005 son totalmente compatibles con la vista de administración dinámica sys.dm_db_index_physical_stats.

Fragmentación de tablas

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 toda una tabla o una parte, 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 agrupado.
La reconstrucción de un índice agrupado 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 de hoja del índice en un orden lógico.
Utilice ALTER INDEX…REORGANIZE para reordenar las páginas de nivel de hoja del índice en un orden lógico. Dado que esta operación se realiza con conexión, 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 agrupado.
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).

Las estadísticas Promedio de bytes libres por página y Promedio de densidad de página (completa) en el conjunto de resultados indican el grado de llenado de las páginas de índice. El valor de Promedio de bytes libres por página debería ser bajo y el valor de Promedio de densidad de página (completa) debería ser alto para un índice que no vaya a tener 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.

[!NOTA] En un índice que contenga muchas inserciones aleatorias y páginas muy llenas se produce un aumento de las divisiones de páginas. Esto aumenta a su vez la fragmentación.

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

Mediante la comparación de los valores de Cambios de extensión y Extensiones examinadas.
El valor de Modificadores de extensión debe ser lo más parecido posible al de Extensiones examinadas. Esta relación se calcula como el valor de Densidad del examen. 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 Fragmentación de examen lógico y Fragmentación de examen de extensión.
El valor de Fragmentación de examen lógico y, en menor medida, el valor de Fragmentación de examen de extensión 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 Fragmentación de examen de extensión es alto si el índice abarca varios archivos. Para reducir estos valores, debe reducir la fragmentación del índice.

Permisos

El usuario debe ser propietario de la tabla o un miembro de la función fija de servidor sysadmin o de las funciones fijas de base de datos db_owner o db_ddladmin.

Ejemplos

A. Presentar la información de fragmentación de una tabla

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

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

B. Usar OBJECT_ID para obtener el identificador de la tabla y sys.indexes para obtener el identificador del índice

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

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

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

D. Mostrar el conjunto de resultados completo para todos los índices de todas las tablas de la base de datos

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

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

E. Usar DBCC SHOWCONTIG y DBCC INDEXDEFRAG para desfragmentar los índices de una base de datos

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

Vea también

Historial de cambios

Versión	Historial
15 de septiembre de 2007	Contenido modificado: Se corrigió la información de bloqueo en la sección Notas. En SQL Server 2005, este comando utiliza sólo un bloqueo de tabla con intención compartida (IS), no un bloqueo S como se indicó previamente. Se aclaró la definición de la columna Filas para los montones.
17 de julio de 2006	Contenido nuevo: Se agregó información acerca del algoritmo para calcular la fragmentación en la sección "Cambios en SQL Server 2005".
5 de diciembre de 2005	Contenido modificado: Se corrigió la descripción de LogicalFragmentation.

Compartir a través de