Iver's web place

En post anteriores, escribí un poco sobre los índices y los tipos de índices que hay en SQL Server 2005. Para continuar con el tema pondré un par de ejemplos para crear y borrar índices, así como identificar cuando están fragmentados y que hacer en ese caso.

Crear y borrar índices

Para saber cuando crear índices y de que tipo, es necesario entender como funcionan los tipos de índices. De forma resumida se puede decir que los índices se almacenan de dos formas, generando en la misma tabla el índice (clustered) y creando una tabla aparte donde vivirán los índices (nonclustered).

Hablando de rendimiento, si tenemos consultas que debemos realizar constantemente con cláusulas ORDER BY o GROUP BY puede servirnos de mucho el crear un índice agrupado (clustered) en el campo afectado. Los índices no agrupados (nonclustered) satisfacen mejor las consultas donde sus campos están incluidos (included) en el índice ya que accede siempre a la misma tabla (la del índice). También el usar índices no agrupados permite tener en diferente file group los índices y las tablas, agilizando el acceso al disco si tenemos cada archivo en diferente unidad.

Es importante saber que en SQL Server no es posible defragmentar una tabla, pero si podemos defragmentar un índice, lo que nos indica que si tenemos un índice agrupado, podremos defragmentar la tabla ya que es la misma donde vive el índice. Crear o eliminar índices tiene sus implicaciones, si tenemos un índice agrupado en una tabla y 4 índices no agrupados apuntando a la misma tabla, al eliminar el índice agrupado se están eliminando y creando los 4 índices no agrupados. Si creamos un índice agrupado sobre una tabla que tiene 3 índices no agrupados, en ese momento se eliminan y se crean nuevamente los índices no agrupados.

Otro concepto interesante es la Cobertura de Índice. Al crear un índice con columnas incluidas, estamos indicando que se almacenaran dichas columnas en la tabla de índices. Si tenemos los campos IdProduct, IdCategory y IdGroup en la tabla "ProductCategory", la Cobertura de Índice puede o no abarcar los 3 campos de acuerdo a nuestras necesidades. La forma de definir dichos índices sería:

En el ejemplo anterior, se definen dos campos como llave y se incluye a IdGroup como parte de la Cobertura del Índice, teniendo en la cobertura los 3 campos. En el caso de enteros no debemos preocuparnos por el límite de tamaño del índice, ya que es de 900 bytes y cada entero puede usar 4 bytes. Pero que pasa si creamos un índice sobre tres campos declarados como sigue:

Podríamos excedernos del límite permitido ya que los tipos de datos nchar y nvarchar requieren 2 bytes por cada caracter. Si creamos un índice con los 3 campos podría excederse de los 900 permitidos, ya que si hacemos el calculo (455 * 2) no queda un excedente de 10 bytes. De esta forma es mejor crear la llave con Title y Revision e incluir a FileName como columna en el índice y nos daría un total de 110 bytes (55 *2) permitiendo que se recupere fácilmente la información de FileName sin ser parte del índice en si.

Identificar índices fragmentados

Cuando las páginas de un índice no están almacenadas en orden se dice que se tienen índices fragmentados. Esto puede ocurrir a nivel de sistema de ficheros o bien a nivel de la base de datos. Para el sistema de ficheros se deben emplear herramientas que salen del alcance del presente artículo. La fragmetnación se da con la actividad diaria y el paso del tiempo y es algo que no podemos evitar realmente, pero se puede controlar y mantener.

Recordando que podemos defragmentar los índices y no las tablas, sabemos que podemos ordenar los datos que tienen un índice (suena lógico que las tablas no debido a que si no tienes un factor por el que ordenar), se almacenaran los datos como le venga en gana al motor de base de datos cuando no se tenga un índice. Para comprobar la fragmentación en SQL Server, podemos usar (o como dicen en el país Vasco, tirar de ..) el comando DBCC SHOWCONTING. Ejemplo:

O bien se puede usar sin parámetros para obtener el resultado de todas las tablas. Este comando desaparece en versiones posteriores a SQL 2005. Por lo que es recomendable utilizar la función del sistema sys.dm_db_index_physical_stats como sigue:

El uso de esta función es mucho más versátil que SHOWCONTING ya que podremos saber la información de todos los objetos de todas las bases de datos de una instancia y muchas cosas más que requieren de un artículo más dedicado para eso. Ahora bien, ya que sabemos los índices fragmentados, podemos tomar diferentes decisiones dependiendo de cada caso:

• Eliminar y crear índices: Esta opción tiene el inconveniente de que mientras se elimina y crea el índice, no estará disponible el índice. En caso de interrupción, se debe iniciar de cero la eliminación/creación del índice y requiere espacio de disco suficiente para mantener una copia de datos de la tabla. Sin embargo la ventaja es que tendrá mayor rendimiento.

• Reconstruir el índice: Esta opción es más optima que la anterior ya que solo se indexa (no se elimina y crea). En SQL Server 2005 se tiene el comando ALTER INDEX REBUILD. En versiones previas se hacia uso de DBCC DBREINDEX, debido a que se elimina en versiones futuras de SQL Server. Otra ventaja del ALTER INDEX REBUILD es que se pude realizar con conexión (ONLINE=ON) y deja el acceso a los usuarios en lo que se trabaja con el índice. Esta opción admite paralelismo.

• Defragmentar el índice: En SQL Server 2000 se introdujo el comando DBCC INDEXDEFRAG que permite a los usuarios seguir accediendo a las tablas (ONLINE=ON), en caso de interrupción permite continuar del punto anterior sin perder el trabajo iniciado (BACK LOG). El comando 'DBCC INDEXDEFRAG desaparece en versiones posteriores a 2005 por lo que sería mejor usar ALTER INDEX REORGANIZE.

Cuando se emplea el comando DBCC INDEXDEFRAG no se actualizan las estadísticas mientras que con DBCC DBREINDEX'' si se actualizan. Puede ser muy útil el uso de cada comando dependiendo de nuestra necesidad, por lo que es recomendable leer la documentación de cada uno.

CONTINUARA...

• Tocando los índices I de III
• Tocando los índices II de III
• Tocando los índices III de III

Para saber más.

• Reorganizando y reconstruyendo índices: http://technet.microsoft.com/en-us/library/ms189858.aspx