SQL Server DBCC INDEXDEFRAG

Comando DBCC INDEXDEFRAG           

Ya hemos establecido, que se trata de un comando de mantenimiento,  cuando mencionamos los Comandos DBCC,  que lleva a cabo la desfragmentación de los índices de la tabla o la vista especificada en el comando. Este comando se ha utilizado desde las primeras versiones de Microsoft SQL Server, que sin bien aún se mantiene en el catálogo de comandos de consola, Microsoft ha indicado que esta característica se quitará en el futuro de Microsoft SQL Server, es por ello que se solicita que para nuevos trabajos de desarrollo no se utilice y se modifique lo antes posible en las aplicaciones que actualmente la utilizan. Se recomienda el uso de la sentencia ALTER INDEX en su lugar.

La desfragmentación se lleva a cabo a nivel hoja del índice para que el orden físico de las páginas coincida con el orden lógico de izquierda a derecha de los nodos hoja, lo que mejora el rendimiento de recorrido del índice.

No se admite el uso de este comando DBCC INDEXDEFRAG en los siguientes objetos:

•          Tablas del sistema
•          Un índice deshabilitado. 
•          Un índice con bloqueo de página establecido en OFF. 
•          Un índice espacial.

La sintaxis de este comando en su forma más simple es:

USE master;

GO

DBCC INDEXDEFRAG ({database}, [table_name | view_name]);

En el comando se indica que se lleve a cabo la desfragmentación de todos los índices de la tabla denominada table_name o de la vista view_name, en la base de datos indicada.  Es necesario indicar que aunque se puede indicar el identificador correspondiente (database_id, table_id o view_id), éste es un número que está asociado al nombre, pero es mas común el uso del nombre correspondiente. Es importante recordar que los nombres  deben seguir las reglas para los identificadores.

Es trascendental mencionar, que si bien muchos comandos DBCC registran actividad en el Log de Microsoft SQL Server, este  comando no genera una entrada ya que no afecta el funcionamiento de la base de datos.

La sintaxis que se usa más a menudo es:

USE master;
GO

DBCC INDEXDEFRAG ({database}, [table_name], index_name);

En el comando se indica el nombre de la base de datos, el nombre de la tabla y el nombre del índice que se va a desfragmentar, en este caso, también es posible colocar el numero asociado del índice (index_id), aunque lo más usual es colocar el nombre, siguiendo las reglas de los identificadores. 

Existen algunos argumentos opcionales que pueden ser utilizados con el comando, estos son:

número_de_partición	Es el número de partición del índice que se va a desfragmentar. Si no se especifica o se especifica 0, la instrucción desfragmenta todas las particiones del índice especificado.
WITH NO_INFOMSGS	Suprime todos los mensajes informativos con niveles de gravedad entre 0 y 10.

Se hace necesario indicar, cuando se ejecuta este comando se puede obtener información correspondiente a actividad realizada por la desfragmentación del índice, a excepción que se haya indicado WITH NO_INFOMSGS, dicha información es:

·         Paginas escaneadas

·         Paginas Movidas

·         Paginas Removidas

Ejemplos de DBCC INDEXDEFRAG

Mostraré algunos ejemplos de uso de este comando:

USE master;
GO

DBCC INDEXDEFRAG (db1, 'Sche1.Table1', index_name) WITH NO_INFOMSGS;

Este comando lleva a cabo la desfragmentación en la base de datos db1 del denominado index_name de la tabla Sche1.Table1  indicada, sin emitir mensajes.

USE master;
GO

DBCC INDEXDEFRAG (db1, 'Sche1.Table1');

Este comando lleva a cabo la desfragmentación en la base de datos db1 de todos los índices de la tabla ‘Sche.Table1’ indicada, en este caso llevará a cabo la emisión de mensajes por cada índice de la tabla, dado que se indica que sean todos los índices.

USE master;
GO

DBCC INDEXDEFRAG (db1, 'Sche1.Table1', index_name);

Este comando lleva a cabo la desfragmentación en la base de datos db1 del denominado index_name de la tabla ‘Sche.Table1’ indicada, en este caso se mostrara el resumen de la operación efectuada al índice

Comentarios

El comando también compacta las páginas de un índice, tomando en cuenta el factor de relleno especificado cuando se creó el índice. Las páginas vacías creadas como consecuencia de esta compactación se eliminan.

A diferencia del comando DBCC DBREINDEX, este comando lleva a cabo una operación en línea. Lo cual indica que no mantiene bloqueos a largo plazo. Por consiguiente, no bloquea la ejecución de consultas o actualizaciones. Es posible que se tarde menos en desfragmentar un índice relativamente poco fragmentado que en generar un índice nuevo porque el tiempo de desfragmentación está relacionado con el volumen de la fragmentación. Sin embargo, un índice muy fragmentado puede tardar mucho más en tiempo desfragmentarse que en volver a generarse.

Cómo se ha indicado, este comando desaparecerá en el futuro, por lo que se recomienda se familiaricen con la sentencia ALTER INDEX, que es la que debe utilizarse y es la que quedará en el futuro.