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