GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

SQL Server FAQ: ¿Es necesario Reindexar o Defragmentar nuestros datos en SQL Server? ¿Qué diferencia existe en Reindexar y Defragmentar?

Volver a: [SQL Server FAQ :: Preguntas y Respuestas Frecuentes de SQL Server :: Manual SQL Server]


Este capítulo explica el problema de la Fragmentación de SQL Server (tanto en el sistema de ficheros como en base de datos, esto es, en los índices). Se explica cómo defragmentar un disco o volumen y cómo manterner defragmentado un disco o volumen. Se explican las consideraciones de las tablas SIN índices (Heap) y de las tablas con índices agrupados (clustered index), cómo comprobar la fragmentación en SQL Server con DBCC SHOWCONTIG y sys.dm_db_index_physical_stats, etc. Por último se explica las alternativas existentes para corregir la fragmentación: Eliminar y volver a crear el índices (DROP INDEX y CREATE INDEX), Recontruir el índice (DBCC DBREINDEX, CREATE INDEX WITH DROP_EXISTING o ALTER INDEX REBUILD) y Defragmentar el índice (DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE).

Para una base de datos es vital evitar el problema de la fragmentación. Esto que pierde importancia en bases de datos pequeñas, es un factor de éxito en bases de datos medianas o grandes con cierta actividad. La fragmentación es uno de los principales problemas de rendimiento con SQL Server.

La fragmentación existente en el sistema de ficheros y/o en la base de datos (tablas y/o índices) puede causar la realización de operaciones de entrada/salida innecesarias, lo cual tiene como resultado un peor rendimiento en las consultas de base de datos, con un peligroso efecto de bola de nieve (las consultas duran más => los bloqueos se mantienen más tiempo => las consultas tardan más por la fragmentación y los bloqueos => etc. ).

El primer problema es conseguir mantener defragmentado el sistema de ficheros. El sistema de ficheros, con la creación, modificación y eliminación de ficheros, se fragmenta, efecto que con el paso del tiempo va empeorando si no se toman medidas.

  • Cómo defragmentar un disco o volumen. Si deseamos defragmentar un disco sobre el que SQL Server tiene algún fichero de base de datos, será necesario parar SQL Server o bien poner Offline las bases de datos que tengan ficheros en el disco o volumen que se desea defragmentar. Del mismo modo, si existe algún otro programa que está accediendo a ficheros sobre dicho disco, es recomendable parar dicho programa, pues el objetivo que se persigue es que en el disco o volumen a defragmentar no exista ningún fichero que esté "pillado" por ninguna aplicación. Después, se puede utilizar la utilidad defrag.exe o la consola Disk Defragmenter, indicando el disco/volumen deseado, para realizar la defragmentación. Es recomendable disponer de al menos un 15% de espacio libre en el disco a defragmentar, pues dicho espacio es utilizado internamente para ordenaciones temporales, etc. Es posible comprobar el estado de fragmentación de un disco o volumen (opción de sólo analizar), por ejemplo, utilizando defrag.exe c: -a (en el caso del disco c:).
  • Mantener defragmentado el disco o volumen. Utilizar ficheros de tamaño fijo (que no se autoextienden automáticamente) dotados del suficiente tamaño, permite evitar la fragmentación. En muchos casos, se utilizan los ficheros por defecto, tal cual MODEL los trajo al mundo, lo cual implica que se crea la base de datos con un tamaño mínimo y va aumentando y aumentando (es decir, fragmentando y fragmentando) según va necesitando espacio. Además, algunos administradores al ver que su base de datos aumenta, de vez en cuanto reducen los ficheros, de tal modo que los ficheros al ser reducidos en cuanto necesiten más espacio volverán a crecer (es decir, y vuelta a fragmentar).

He leído en algún foro que existen herramientas capaces de defragmentar a nivel de archivo, a diferencia de las herramientas que vienen de serie con Microsoft Windows, que defragmentan el sistema de ficheros completo de un disco o volumen. Del mismo modo, también he leído que exiten herramientas capaces de defragmentar incluso ficheros que están "pillados" por aplicaciones (es decir, que están en uso, bloqueados por la aplicación que está accediendo). Entre otros, he oído hablar de Diskeeper, pero no lo he podido probar. La idea, es que si tenemos un problema de fragmentación correctamente identificado sobre un fichero en particular, poder defragmentar sólo el fichero afectado, o bien, poder defragmentar con el fichero en uso (ej: con SQL Server levantado y dando servicio), permitiría minimizar o eliminar el tiempo de parada del servicio. Insisto... suena muy bien, pero no lo he podido probar, y también dicen que el tiempo de defragmentación con ficheros en uso sobrecarga el sistema y sólo es recomendable en periodos de poca actividad.

Una vez solucionados los problemas de disco, continuamos con SQL Server. Del mismo modo que ocurría con el sistema de ficheros, la base de datos (sus tablas y/o índices, como ahora veremos) también se puede fragmentar con la actividad diaria y el paso del tiempo.

Dentro de la base de datos, lo primero que se debe tener muy claro, es que no se pueden defragmentar ni reindexar las tablas: sólo es posible defragmentar o reindexar los índices. Esta afirmación, que a priori puede parecer al menos atrevida, tiene un razonamiento bastante sensato:

  • Una tabla sin índices utiliza una estructura de montón (Heap). Al contrario que otras estructuras de datos, y estamos hablando desde un punto de vista algorítmico, una estructura de montón (Heap) no tiene un orden lógico en sus datos, algo que no ocurre en otras estructuras como los Árboles B. El objeto de la defragmentación es colocar los datos ordenados y juntos... pero claro... ¿y si no hay criterio para ordenarlos?
  • Un tabla con un índice agrupado (clustered index) convierte a la tabla en el índice. Genial, porque ahora si podremos defragmentar o indexar la tabla... digo el índice... bueno, que más da si es casi lo mismo en este caso!! A fin de cuentas, es una buena práctica que todas las tablas tengan al menos un índice, y en particular, al menos tengan un índice agrupado (clustered index) y los índices no agrupados que se necesite (sin pasarse, que el rendimiento no perdona).

Con esto, hemos dado otro pasito: Sabemos que sólo se pueden defragmentar y/o reindexar los índices, y no las tablas, excepto que se trate de tablas con índices agrupados (clustered index). En caso de tener una tabla sin índice agrupado (clustered index) pero con uno o varios índices no agrupados, sólo podremos reindexar o defragmentar los índices no agrupados. Sin embargo existe un truquito: para defragmentar una tabla sin índice agrupado (es decir, un montón o Heap), es posible crear un índice agrupado (clustered index) sobre dicha tabla y seguidamente quitar dicho índice.

Para comprobar la fragmentación de una tabla o índice en SQL Server, podemos utilizar el comando DBCC SHOWCONTIG. Así, si tenemos una tabla denominada TBL_HIS_FACTURACION en una base de datos FACTURAS, podríamos ejecutar un código como el siguiente para comprabar la fragmentación:

USE FACTURAS
GO

DBCC SHOWCONTIG('TBL_HIS_FACTURACION')
GO

También es posible ejecutar DBCC SHOWCONTIG sin parámetros, para obtener como salida la información de fragmentación de todas las tablas de la base de datos en uso.

Los valores de Logical Scan Fragmentation y de Extent Scan Fragmentation informan del nivel de fragmentación de una tabla, debiendo ser lo más próximos a cero (entre 0 y 10).

Los valores de Avg. Bytes free per page y de Avg. Page density (full) informan del estado del llenado de las páginas del índice (el primero debe ser un valor bajo y el segundo alto).

Sin embargo, DBCC SHOWCONTIG desaparecerá en futuras versiones, por lo que a partir de SQL Server 2005 la recomendación es utilizar la función del sistema sys.dm_db_index_physical_stats. A continuación se muestra un ejemplo para ver la información de la misma tabla del ejemplo anterior:

USE FACTURAS
GO

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'TBL_HIS_FACTURACION'), NULL, NULL, 'LIMITED')
GO

Existen varias diferencias entre DBCC SHOWCONTIG y sys.dm_db_index_physical_stats, que no entran en el alcance de éste artículo. Una diferencia que me pareció interesante, es que con sys.dm_db_index_physical_stats podemos ver la información de fragmentación de todos los objetos de todas las bases de datos de una instancia de SQL Server, ejecutando select * from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED').

Finalmente, una vez que hemos encontrado algún índice fragmentado, tenemos distintas alternativas:

  • Eliminar y volver a crear el índice agrupado de la tabla (DROP INDEX y CREATE INDEX). Si la tabla contiene un índice agrupado, esta es la alternativa más lenta, pero la que ofrece un mejor rendimiento. Al eliminar y volver a crear el índice agrupado, se volverán a reconstruir también los índices no agrupados de la tabla. El inconveniente, es que el índice estará sin conexión (no estará disponible) y la operación es atómica (si se interrumpe, será necesario empezar de nuevo desde el principio). Además, se necesita disponer de espacio suficiente para mantener una copia de los datos de la tabla.
  • Recontruir el índice (DBCC DBREINDEX, CREATE INDEX WITH DROP_EXISTING o ALTER INDEX REBUILD). En el caso de los índices agrupados (clustered index) es mucho más óptimo que eliminar el índice (DROP INDEX) y volver a crearlo (CREATE INDEX), pues los índices no agrupados sólo se reconstruirán una única vez en vez de dos veces (una con el DROP INDEX y la otra con el CREATE INDEX). Para esta alternativa, se necesita utilizar el comando DBCC DBREINDEX, o el comando CREATE INDEX WITH DROP_EXISTING, o el comando ALTER INDEX REBUILD. El comando DBCC DBREINDEX desaparecerá en futuras versiones, por lo que la recomendación es utilizar ALTER INDEX REBUILD. El comando ALTER INDEX es nuevo en SQL Server 2005, ya que en ediciones anteriores sólo existían los comandos CREATE INDEX y DROP INDEX, pero no existía ALTER INDEX. Es una operación atómica. Hasta SQL Server 2000, implica que el índice estará sin conexión (no estará disponible). Desde SQL Server 2005, y al utilizar ALTER INDEX, se puede realizar con conexión (opción ONLINE=ON, manteniendo el acceso de los usuarios) o sin conexión. También necesita disponer de espacio suficiente. Esta operación admite paralelismo.
  • Defragmentar el índice (DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE). SQL Server 2000 introdujo el comando DBCC INDEXDEFRAG, que permite defragmentar sólo los nodos hoja de los índices y no soporta paralelismo, pero tiene la ventaja de ser una operación en línea (los usuarios pueden seguir accediendo a nuestras tablas e índices). Permite la realización de operaciones como BACKUP LOG mientras está en marcha (potencialmente beneficioso para tablas muy grandes), y además, en caso de verse interrumpido, puede continuarse sin perderse el trabajo realizado. Dependiendo de la fragmentación, DBCC INDEXDEFRAG puede ser considerablemente más rápido (o también más lento). El comando DBCC INDEXDEFRAG desaparecerá en futuras versiones, por lo que la recomendación es utilizar ALTER INDEX REORGANIZE.

También es importante tener en cuenta que con DBCC INDEXDEFRAG no se actualizan las estadísticas, mientras que con DBCC DBREINDEX si se actualizan estadísticas.

En cualquier caso, aunque DBCC DBREINDEX, CREATE INDEX WITH DROP_EXISTING y ALTER INDEX REBUILD puedan parecer iguales, del mismo modo que DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE también puedan parecer iguales, en caso de necesidad revisar la documentación, pues sí existen pequeñas diferencias (ej: poder cambiar o no la definición del índice, etc.).

Como conclusión, además de trabajar con ficheros de tamaño fijo, es importante planificar las reconstruciones de índices o defragmentaciones de índices que se consideren necesarias. La forma más sencilla, quizás sea un Plan de Mantenimiento nocturno, planificado una vez a la semana, o con la periodicidad que consideremos más apropiada. En bases de datos grandes o medianas, será necesario "hilar más fino".

Volver a: [SQL Server FAQ :: Preguntas y Respuestas Frecuentes de SQL Server :: Manual SQL Server]


[Fecha del Artículo (UTC): 09/01/2008]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas