Una tarea habitual en un Data Warehouse, como parte de los procesos de carga y transformación de datos (ETL), es la creación de índices. Son muchos los aspectos que debemos estudiar al respecto, y con el objetivo de optimizar nuestros procesos de carga y el rendimiento final del sistema que se está diseñando.
El Modo de Recuperación es un factor vital en el rendimiento, lo que en ocasiones obliga a configurar las bases de datos del propio Data Warehouse en Modo de Recuperación Sencillo (o de Copia Masiva), o bien, mantenerlas en Modo de Recuperación Completo, y utilizar el Modo de Recuperación Sencillo en otras bases de datos previas (Temporales y de Stagging) sobre las que descargar una buena parte del coste de los procesos de carga. Aunque en principio, no es recomendable utilizar el Modo de Recuperación Sencillo para entornos de producción, si existe un entorno en el cual cabe la posibilidad de saltarse esta regla, es en un Data Warehouse, bajo la premisa de que ante una eventual pérdida de datos, siempre será posible recuperar el último backup completo, y seguidamente volver a ejecutar los procesos de carga posteriores al momento de recuperación. Evidentemente, habría que estudiar cada caso, pero cierto es que cabe la posibilidad.
Un correcto dimensionamiento de los ficheros de la base de datos también resulta fundamental, evitando así el esfuerzo de los crecimientos de los ficheros. Existen más detalles, como mantener el sistema sin Fragmentación (sin en base de datos ni en el Sistema de Ficheros), suficiente memoria, etc.
Otro tema importante, es la elección del tipo de índice más apropiado. Hasta SQL Server 2000, principalmente podíamos decidir entre:
- Índices Agrupados (CLUSTERED). Cualquier tabla, puede tener ninguno o un índice agrupado (CLUSTERED). Los índices agrupados son la propia tabla, es decir, cuando creamos un índice agrupado sobre una tabla, estamos convirtiendo a la propia tabla en un índice, lo cual tiene varias implicaciones.
A efectos de rendimiento, es importante tener en cuenta, que una tabla con un índice agrupado se almacenará de forma ordenada, lo cual, facilitará la satisfacción de consultas con cláusulas ORDER BY o GROUP BY coincidentes con los campos del índice, por poner un ejemplo representativo.
- Índices No Agrupados (NONCLUSTERED). Se trata de estructuras ajenas a la propia tabla, que consumen su propio almacenamiento. Esto permite, que la tabla se pueda almacenar sobre un Grupo de Ficheros (File Group) y los índices no agrupados sobre un Grupo de Fichero diferente (por ejemplo, sobre un volumen distinto al que se accede por un camino de fibra separado). Es posible crear índices no agrupados, sobre tablas sin índices y sobre tablas con índices (incluso aunque tenga un índice agrupado).
Los índices No Agrupados, suelen ser especialmente efectivos para satisfacer cláusulas WHERE sobre los campos del índice que devuelven un conjunto muy pequeño de registros. También son muy apropiados para satisfacer consultas cuyos campos están todos incluidos en el índice (de este modo, no es necesario acceder a la tabla, pues toda la información está en el índice).
Esta introducción a los índices agrupados y no agrupados es muy básica. Si los estudiamos más a fondo, podemos ver consideraciones importante varias, como por ejemplo la defragmentación. En SQL Server no es posible defragmentar una tabla, sólo un índice. Otra cosa, es que la tabla tenga un índice agrupado, en cuyo caso, al defragmentar dicho índice estaremos defragmentando la tabla.
También hay consideraciones en la creación y eliminación de índices, así como en la reindexación. Si tenemos un índice agrupado y múltiples índices no agrupados, si eliminamos el índice agrupado, estaremos implícitamente eliminando y volviendo a crear los siete índices no agrupados. Del mismo modo, si volvemos a crear el índice agrupado, volveremos a eliminar y crear los siete índices no agrupados de forma implícita. Esta problemática, es el motivo de que existan sentencias como CREATE INDEX DROP EXISTING, ALTER INDEX REBUID o DBCC DBREINDEX.
Es importante tener claro la importación de Reindexar y Defragmentar índices en SQL Server, así como las diferentes alternativas que tenemos para realizar estas tareas.
Podemos seguir viendo detalles, como el Factor de llenado, el orden de los campos en el índice, la satisfacción de los planes de ejecución, las estadísticas de los índices, índices únicos o no únicos, utilizar campos NULL en índices, etc. Un estudio más avanzado, queda fuera del alcance de este artículo (requeriría un libro completo), aunque si quisiera intentar transmitir la existencia de estos detalles para poder tenerlos presentes.
Con SQL Server 2005, se introduce la inclusión de índices no agrupados, a través de la sentencia CREATE NONCLUSTERED INDEX INCLUDE. Para entenderlo mejor, es importante conocer el concepto de Cobertura de Índice.
El concepto de Cobertura de Índice está relacionado con qué campos de una consulta están contenidos en un índice. Así, si todos los campos de una consulta están contenidos en un índice, es posible que dicha consulta se resuelva utilizando dicho índice, sin necesidad de acceder a la propia tabla (pa qué, si ya lo tiene todo en el índice). Una técnica de optimización de base de datos, es ampliar la Cobertura de Índice. Si tenemos una consulta que para resolverse completamente a través de un índice, requiere agregar a dicho índice un campo adicional de la tabla, la acción de alterar dicho índice para agregar un nuevo campo, se denomina ampliar la Cobertura de Índice. Como siempre, habría que estudiar cada caso, para ver hasta que punto sale rentable, pero bueno, el concepto es este.
Entendido qué es la Cobertura de Índice, ahora será más fácil entender los índices no agrupados con inclusión. Este tipo de índices, permiten incluir campos adicionales que serán almacenados en los nodos hoja (estos son los campos especificados en la cláusula INCLUDE). Estos campos no serán propiamente indexados, pero si una consulta los requiere podrá recuperarlos directamente del índice sin necesidad de realizar un Lookup (una búsqueda) sobre la correspondiente fila de la correspondiente tabla. En las pruebas que he realizado, y en los casos en que he aplicado este tipo de índices en entornos de Producción, si es cierto que existen casos en los que se consiguen mejoras de rendimiento interesantes.
A continuación, se muestran unas pruebas representativas de creaciones de índices, algo muy habitual en un entorno de Data Warehouse, en algunas ocasiones porque creamos nuevas tablas que necesitamos indexar (aunque sean tablas temporales), en otras ocasiones resulta más rápido eliminar los índices de una tabla antes de cargarla (insertarla y actualizarla por doquier) volviendo a crear los índices al final del proceso.
Sin más, pasamos a ver los resultados de las pruebas realizadas.
Creación de índices No Agrupados (NONCLUSTERED) sobre tabla sin índices (Heap)
Los resultados obtenidos utilizando el Modo de Recuperación Completo (Recovery FULL) han sido los siguientes:
- Creación índice No Agrupado sobre dos campos VARCHAR(45). Tiempo de ejecución de 8 segundos. La base de datos crece desde su tamaño inicial de 58MB de Datos y 1MB de Log a 89MB de Datos y 29MB de Log.
Si la base de datos tenía suficiente espacio de Datos y Log (es decir, está correctamente dimensionada), el tiempo de ejecución se limita a 3 segundos.
- Creación índice No Agrupado sobre un campo entero (INT). Tiempo de ejecución de 4 segundos. La base de datos crece desde su tamaño inicial de 58MB de Datos y 1MB de Log a 61MB de Datos y 5MB de Log.
Si la base de datos tenía suficiente espacio de Datos y Log (es decir, está correctamente dimensionada), el tiempo de ejecución se limita a 2 segundos.
Los resultados obtenidos utilizando el Modo de Recuperación Sencillo (Recovery SIMPLE) han sido los siguientes:
- Creación índice No Agrupado sobre dos campos VARCHAR(45). Tiempo de ejecución de 4 segundos. La base de datos crece desde su tamaño inicial de 58MB de Datos y 1MB de Log a 85MB de Datos y 2MB de Log.
Si la base de datos tenía suficiente espacio de Datos y Log (es decir, está correctamente dimensionada), el tiempo de ejecución se limita a 2 segundos.
- Creación índice No Agrupado sobre un campo entero (INT). Tiempo de ejecución de 2 segundos. La base de datos crece desde su tamaño inicial de 58MB de Datos y 1MB de Log a 61MB de Datos y 1MB de Log.
Si la base de datos tenía suficiente espacio de Datos y Log (es decir, está correctamente dimensionada), el tiempo de ejecución se mantiene en 2 segundos.
A continuación se incluye código Transact-SQL de ejemplo, similar al utilizado en la prueba:
CREATE UNIQUE NONCLUSTERED INDEX IX_Test1
ON dbo.pruebas_indice(Campo01, Campo02)
CREATE UNIQUE NONCLUSTERED INDEX IX_Test2
ON dbo.pruebas_indice(Campo03)
|
Creación de índices Agrupados (CLUSTERED) sobre tabla sin índices (Heap)
Los resultados obtenidos utilizando el Modo de Recuperación Completo (Recovery FULL) han sido los siguientes:
- Creación índice No Agrupado sobre dos campos VARCHAR(45). Tiempo de ejecución de 12 segundos. La base de datos crece desde su tamaño inicial de 58MB de Datos y 1MB de Log a 123MB de Datos y 62MB de Log.
Si la base de datos tenía suficiente espacio de Datos y Log (es decir, está correctamente dimensionada), el tiempo de ejecución se limita a 3 segundos.
- Creación índice No Agrupado sobre un campo entero (INT). Tiempo de ejecución de 20 segundos. La base de datos crece desde su tamaño inicial de 58MB de Datos y 1MB de Log a 124MB de Datos y 62MB de Log.
Si la base de datos tenía suficiente espacio de Datos y Log (es decir, está correctamente dimensionada), el tiempo de ejecución se limita a 5 segundos.
Los resultados obtenidos utilizando el Modo de Recuperación Sencillo (Recovery SIMPLE) han sido los siguientes:
- Creación índice No Agrupado sobre dos campos VARCHAR(45). Tiempo de ejecución de 7 segundos. La base de datos crece desde su tamaño inicial de 58MB de Datos y 1MB de Log a 124MB de Datos y 3MB de Log.
Si la base de datos tenía suficiente espacio de Datos y Log (es decir, está correctamente dimensionada), el tiempo de ejecución se limita a 3 segundos.
- Creación índice No Agrupado sobre un campo entero (INT). Tiempo de ejecución de 9 segundos. La base de datos crece desde su tamaño inicial de 58MB de Datos y 1MB de Log a 123MB de Datos y 2MB de Log.
Si la base de datos tenía suficiente espacio de Datos y Log (es decir, está correctamente dimensionada), el tiempo de ejecución se limita a 3 segundos.
A continuación se incluye código Transact-SQL de ejemplo, similar al utilizado en la prueba:
| CREATE UNIQUE CLUSTERED INDEX IX_Test1 ON dbo.pruebas_indice(Campo01, Campo02) CREATE UNIQUE CLUSTERED INDEX IX_Test2 ON dbo.pruebas_indice(Campo03) |
Conclusiones
Por último, sólo incluir una valoración adicional. Si bien estamos encontrando diferencias de unos pocos segundos, el problema que realmente debemos evaluar, es la extrapolación de estos resultados a un entorno de Data Warehouse real, donde trabajaremos con tablas mucho más grandes, y dónde posiblemente nuestros procesos de carga se ejecuten simultáneamente con los procesos de carga de otros DataMart desarrollados por otros compañeros, empresas, o quizás incluso por nosotros mismos (guerra de recursos).
Reducir un tiempo de ejecución de 8 segundos a 2 segundos, no nos lleva a ninguna parte. Reducir un tiempo de ejecución de 8 horas a 2 horas, es algo muy distinto.
