jump to navigation

msSQL – Consejos para Reconstruir Índices

Fuentehttp://www.sql-server-performance.com/tips/rebuilding_indexes_p2.aspx

Autor: Brad McGehee

Periódicamente (diariamente, semanalmente, o mensualmente) realiza una reorganización de la base de datos de todos los índices de todas las tablas en tu base de datos. Esto reconstruirá los índices de modo que los datos ya no estarán fragmentados. Los datos fragmentados pueden causar que SQL Server realice lecturas de datos innecesarias, decrementando el rendimiento de SQL Server.

Si haces una reorganización en una tabla con índices agrupados, algunos índices no-agrupados en la misma tabla serán automáticamente reconstruidos.

La reorganización de la base de datos puede ser realizada usando el Maintenance Wizard, o corriendo tu propio script a través del SQL Server Agent (observa debajo).

El comando DBCC DBREINDEX no reconstruirá automáticamente todos los índices de las tablas en la base de datos; solo puede funcionar de a una tabla por vez. Pero si corres el siguiente script, puedes indexar todas las tablas en una base de datos con facilidad.

–Script para reindexar todas las tablas en una base de datos automáticamente.

USE DatabaseName –Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Este script reindexará automáticamente todos los índices de todas las tablas de una base de datos seleccionada, y proporciona un factor de relleno del 90%. Puedes sustituir cualquier número adecuado para cubrir el factor del script de arriba.

Cuando DBCC DBREINDEX es utilizado para reconstruir índices, mantén en mente que como los índices en las tablas están siendo reconstruidos, esas tablas se volverán no disponibles para el uso de los usuarios.

Por ejemplo, cuando un índice no agrupado es reconstruido, un bloqueo para compartir la tabla es puesto sobre la misma, previniendo todas las operaciones SELECT sobre la misma. Cuando un índice agrupado es reconstruido, un exclusivo bloqueo de tabla es puesto sobre la misma, previniendo el acceso a la tabla por los usuarios. Por esto, deberías correr este comando solamente cuando los usuarios no necesitan acceder a las tablas que van a ser reorganizadas. [7.0, 2000, 2005] Actualizado 7-24-2006

Cuando creas o reconstruyes un índice, especificas un factor de relleno, el cual es la suma de las páginas de datos en los índices  que están llenos. Un factor de relleno de 100, significa que cada índice de pagina esta 100% lleno, un factor de relleno del 50% significa que cada índice de pagina es lleno en un 50%. Si creas un índice agrupado que tiene un factor de relleno del 100%, y no se basa en una clave monótonamente creciente, significa que cada vez  que se inserta un registro(o quizás se actualiza), las divisiones de página pueden ocurrir porque no hay lugar para los datos en las páginas existentes. Numerosas divisiones de páginas pueden decrementar el rendimiento de SQL Server.

Aquí hay un ejemplo: Asumiendo que ya has creado un nuevo índice en una tabla, con el factor de relleno por defecto. Cuando SQL Server crea un índice, coloca el índice en páginas físicas contiguas, las cuales permiten un óptimo acceso de E/S porque los datos pueden ser leídos secuencialmente. Pero como la tabla crece y cambia con los INSERTS, UPDATES y DELETES, ocurre la división de páginas. Cuando las páginas se dividen, SQL Server debe alojar nuevas páginas en otras partes del disco, y esas nuevas páginas no son contiguas con las páginas físicas originales. Por esto, cualquier E/S, accesos E/S no secuenciales deben ser usados para unir los datos, lo cual es mucho más lento, para acceder a los índices de las páginas.

Por ende, ¿cual es el factor de relleno ideal?. Eso depende del margen de lecturas y escrituras que tu aplicación le hace a tus tablas de SQL Server. Como ayuda memoria, sigue estos ítems.

Debes experimentar para encontrar el factor de relleno óptimo para tu aplicación en particular. No asumas que un bajo nivel de relleno es siempre mejor que uno alto. Mientras las divisiones de páginas sean reducidas, con un bajo factor de relleno, se incrementa el número de páginas que tiene que leer SQL Server durante las consultas, lo cual reduce el rendimiento. No solo es un aumento de gastos generales de E/S, con la disminución del factor de relleno, sino también afecta el buffer cache. Como las páginas de datos son movidas desde el disco hasta el buffer, la página entera (incluidos los espacios vacíos) son movidos al buffer. Así que cuanto más bajo es el factor de relleno, más páginas deben ser movidas al buffer de SQL Server, lo que significa que hay menos lugar para otras páginas de datos importantes que pueden ocupar el mismo lugar en ese mismo momento, lo cual reduce el rendimiento.

Si no necesitas especificar el factor de relleno, el factor de relleno predeterminado es 0, lo que significa que es igual a un factor de relleno del 100%, (la hoja de páginas del índice son llenadas al 100%). En la mayoría de los casos, este valor por defecto no es una buena elección, especialmente para índices agrupados. [6.5, 7.0, 2000, 2005] Actualizado 7-24-2006

Si encuentras que tu registro de transacciones crece hasta un tamaño inaceptable cuando se ejecuta DBCC REINDEX, puedes minimizar el crecimiento cambiando de modo Full Recovery al modo Bulk-logged antes de que reindexes, y cuando esté hecho, vuelve al modo anterior. Esto significará una reducción del tamaño del registro de transacciones. [2000, 2005] Actualizado 7-24-2006

Si tienes una tabla que tiene un índice agrupado con una clave que crece o decrece monótonamente, y si la tabla no está sujeta en UPDATES o si no tiene columnas VARCHAR, entonces el factor de relleno ideal para la tabla es 100. Esto es porque cada tabla normalmente no tendrá divisiones de páginas. Y porque el factor de relleno es 100, SQL Server podrá recurrir poco a lecturas y escrituras I/O en la tabla, y el desempeño será incrementado. [7.0, 2000, 2005] Actualizado 7-24-2006

Si no estás seguro de cómo hacer el factor de relleno para tus índices, tu primer paso es determinar la proporción de escrituras y lecturas en el disco. El camino es usar estos dos contadores: Physical Disk Object:% Tiempo de lectura de disco y Physical Disk Object:%Tiempo de escritura. Cuando corres ambos contadores en un arreglo, deberías tener una buena impresión sobre qué porcentaje de tus E/S son lecturas y escrituras. Si tu porcentaje de escrituras excede ampliamente el porcentaje de lecturas, entonces un factor de relleno menor es necesario, si el porcentaje de lecturas excede ampliamente el porcentaje de escrituras, entonces un factor de relleno mayor es necesario.

Otro contador Monitor de Rendimiento que puedes usar para ayudarte a seleccionar el factor de relleno ideal para tu entorno es el SQL Server Acces Methods: Páginas Divididas/Segundo. Este contador mide el número de páginas divididas que suceden en SQL Server en cada segundo. Para un mejor rendimiento, que este contador sea lo más lento posible. Si el contador es muy lento, entonces debes decrementar el factor de relleno para prevenir nuevas páginas divididas. Si es el contador es muy bajo, entonces el factor de relleno que tienes está bien, o podría ser un poco mas pequeño. No lo sabrías a menos que incrementes el factor de relleno y mires los resultados.

Idealmente, quieres un factor de relleno que prevenga la excesiva división de páginas, pero no tan bajo como para incrementar el tamaño de la base de datos, lo cual puede reducir el rendimiento de las lecturas por todas las páginas extra de datos que necesitan ser leídas.

Una vez que conoces la relación de lecturas y escrituras del disco, ahora tienes la información que te ayudará a determinar el nivel de relleno óptimo para tus índices. [6.5, 7.0, 2000, 2005] Actualizado 7-24-2006

Si quieres determinar el nivel de fragmentación de tus índices, debido a la división de páginas, puedes correr el comando DBCC SHOWCONTIG. Para este comando necesitas saber el ID de ambas tablas y el índice a ser analizado. Podrías correr el siguiente script:

–Script to identify table fragmentation

–Declare variables
DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)

–Set the table and index to be examined
SELECT @IndexName = ‘index_name’ –enter name of index
SET @ID = OBJECT_ID(‘table_name’) –enter name of table

–Get the Index Values
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName

–Display the fragmentation
DBCC SHOWCONTIG (@id, @IndexID)

Mientras el comando  DBCC SHOWCONTIG provee varias medidas, la clave principal es Scan Density. Este debería estar lo más cerca de 100% como sea posible. Si el scan density es menor que el 75% entonces deberías reindexar las tablas en tu base de datos, y también incrementar el factor de relleno si estás encontrando que el actual factor de relleno que estás usando no es apropiado. [6.5, 7.0, 2000] Actualizado 7-24-2006

Aquí hay un script que es usado para crear el comando DBCC SHOWCONFIG para todos los  índices en una o más tablas. Una vez que corras este script, producirá para vos una sentencia DBCC SHOWCONFIG por cada índice, los cuales puedes luego correr para saber qué nivel de fragmentación tienen tus índices. Este script es especialmente útil si no sabes los nombres de los índices en tus tablas (cosa que ocurre todo el tiempo).

SELECT ‘dbcc showcontig (‘ +
CONVERT(varchar(20),i.id) + ‘,’ + — table id
CONVERT(varchar(20),i.indid) + ‘) — ‘ + — index id
object_name(i.id) + ‘.’ + — table name
i.name — index name
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.type = ‘U’
and i.indid < 2
and
i.id = object_id(o.name)
ORDER BY
object_name(i.id), i.indid

Una vez que corres el script, las salidas serán sentencias DBCC SHOWCONFIG por cada una de las tablas e índices. Esta salida puede luego ser cortada y pegada en el Analizador de Consultas o Mangement Studio y ser corrida, lo cual produce un resultado DBCC SHOWCONFIG por cada índice de cada tabla que especificaste.

[7.0, 2000, 2005]Actualizado 11-1-2005 Tip brindado por Jeff M. Belina and Steven R. Morrow

No re-indexar tus tablas cuando tu base de datos está activa y en producción, ya que puede bloquear los recursos y hacer que sus usuarios tengan problemas.  La Re-indización  debe programarse en los momentos en que la BD esté abajo (desactivado), o en el peor de los casos, mientras el uso de la misma sea reducido.

Si utilizas el comando CREATE INDEX para crear o reconstruir tus índices, la opción FILLFACTOR tiene su propia sub-opción llamada PAD_INDEX. Si no se especifica la opción PAD_INDEX, entonces el FILLFACTOR sólo se aplica a la hoja de páginas en el índice, no en el índice de páginas intermedias. Sin embargo, si especificas PAD_INDEX a lo largo de la opción FILLFACTOR, cuando el índice se crea, el FILLFACTOR se aplicará a las páginas de índice intermedio.

Si deseas reconstruir un índice cluster utilizando el comando CREATE INDEX, y suponiendo que la tabla también tiene índices non cluster, los mejores resultados se obtienen cuando se utiliza también la opción DROP_EXISTING, junto con el comando CREATE INDEX. La opción  DROP_EXISTING incluye optimizaciones que impiden la reconstrucción de alguno de los índices no agrupados dos veces.

SQL Server 2000 tiene un comando llamado DBCC INDEXDEFRAG, que se utiliza para desfragmentar índices cluster y non cluster en una tabla o en vistas indexadas. Esto se logra mediante la compactación y la desfragmentación de la hoja nivel del índice a fin de que el orden físico de las páginas del índice coincide con el orden lógico de los nodos, lo que aumenta el rendimiento. Utilizar DBCC INDEXDEFRAG en lugar de DBCC DBREINDEX suele ser beneficioso ya que este comando no incluye bloqueos de la BD por largos períodos de tiempo como DBCC DBREINDEX. Esto significa que se pueden ejecutar durante el periodo que la BD está en producción sin afectar significativamente el rendimiento, a pesar de que cualquier tarea de mantenimiento de este tipo debería, idealmente, ser programada durante los tiempos de parada o de bajo uso de la misma.

En el lado negativo, DBCC INDEXDEFRAG  tarda más tiempo en ejecutarse que DBCC REINDEX, y las estadísticas no se actualizan automáticamente. Esto significa que si utiliza DBCC INDEXDEFRAG, también tendrá que ejecutar UPDATE STATISTICS.

Una manera de acelerar el re-indexado de tus bases de datos es estar seguro de que tu base de datos SQL Server y los archivos de log  se encuentran físicamente desfragmentador, antes de reindexar tu base de datos. Al asegurarte que tu base de datos y archivos de registro son contiguos (desfragmentados), el re-indexado no sólo será más rápido, sino que requerirá menos recursos de E/S, ayudando al rendimiento global de SQL Server. Si utiliza Windows 2000 o 2003, una utilidad de desfragmentación  está disponible para este fin, aunque solo desfragmentará la base de datos SQL Server y los archivos de log cuando estén cerrados. Idealmente, debes usar una utilidad diseñada para desfragmentar bases de datos SQL Server  y archivos de log abiertos.

Según Microsoft, el número total de páginas en una tabla afecta el rendimiento de SQL Server como la fragmentación. Por ejemplo, si una tabla tiene menos de 100 páginas de datos, reindexar la misma  para eliminar la fragmentación no va a beneficiar el rendimiento. Esto se debe a que existen otras cosas, tales como las caches por hardware, el cacheo de SQL Server y la funcionalidad de lectura adelantada de SQL Server , las cuales ocultaran el efecto negativo de la fragmentación. Por otra parte, grandes tablas pueden verse beneficiadas con el reindexado, pues debido a su tamaño la fragmentación puede afectar negativamente a la E/S de disco, perjudicando el rendimiento.

Muchas veces, sería útil disponer de una versión más pequeña de la base de datos que se encuentra en producción para realizar pruebas de rendimiento. Esto se debe a que a menudo es difícil tener un sistema de pruebas lo suficientemente grande como para igualar a la base de datos en producción. Lamentablemente, el único problema con esto es que si tomas una gran base de datos de producción y la truncas, provocarás cambios en sus estadísticas, y ya no podrás utilizarla para realizar pruebas de rendimiento fiables, ya que  el índice de las estadísticas será diferente entre las dos bases de datos, y  posiblemente resulte en  diferentes planes de ejecución.

Afortunadamente, existe un trabajo sobre este problema, y es que una vez que se crea una versión truncada de una gran base de datos que está en producción, tú puedes literalmente mover las estadísticas de la base de datos de producción a la base de datos de prueba, lo que significa que los planes de ejecución para ambas bases de datos será el mismo, permitiendo así que los resultados obtenidos de  testear las performances de la BD sean correctos.


Conclusión:

Abril 2009 

García, Maximiliano         (3801-1812)
Giudice, Matias                 (3801-1293)
Hollmann, Santiago         (3801-1435)
Pariso, Mariela                (3801-0307)
Osorio, Daniela               (3801-0364)
Ufor,Pablo Nahuel          (3801-1197)

Sabemos que un índice es una estructura física de acceso, que se especifica con base en uno o más atributos de un archivo. En SQL, un archivo corresponde, aproximadamente, a una relación, de modo que los índices se especifican sobre relaciones. El atributo o atributos sobre los cuales se crea un índice se denominan atributos de indización. Los índices hacen más eficiente el acceso a tuplas, es decir, la ejecución de una consulta tardará menos si alguno de los atributos implicados en las condiciones de la consulta está indizado.

SQL ofrece dos opciones adicionales al crear índices. La primera permite especificar la restricción de clave sobre el o los atributos de indización, es decir, indica que los atributos seleccionados como índice, son también los atributos clave de la relación en cuestión. Y la segunda opción consiste en crear un índice que permite especificar si se trata o no de un índice de agrupamiento, es decir, las tuplas podrán estar indizadas por determinado atributo, el cual, además, será un criterio de agrupamiento de las mismas. Éstos son llamados índices cluster o agrupados, y hacen más eficiente las operaciones sobre las relaciones.

El problema de los índices radica en que, más allá de que sean favorables en términos de agilidad para las consultas, su mantenimiento resulta costoso cada vez que se actualiza la relación en cuestión, y en que requieren almacenamiento adicional.

En relación a ello, el artículo de SQL Server trata acerca de ciertos consejos que se dan a los usuarios para reconstruir los índices de las tablas de sus bases de datos, a efectos de aumentar la eficiencia del sistema.

Consideramos una tarea favorable la de reconstruir los índices de las tablas, pues esto ayudará a un mejor aprovechamiento del espacio de almacenamiento y a una mejor organización de los índices, esto es, ayudará a desfragmentar los datos, evitando lecturas de datos innecesarias y el decremento del rendimiento de SQL Server.

Por otra parte, el artículo da una orientación para la elección del factor de relleno que se aplicará a las tablas de una determinada base de datos.

El factor de relleno corresponde con el porcentaje de “llenura” que poseen las páginas de índice de las tablas, es decir, un factor de relleno de 100, por ejemplo, significa que cada página de índices está 100% llena. En cuanto a esto será preciso analizar cada caso en particular.

No es posible encontrar un factor de relleno ideal, aplicable a todos los casos, pues ello depende del uso que se le de a la base de datos. Lo que sucede es que, si se crea un índice agrupado, por ejemplo, que tiene un factor de relleno de 100%, cada vez que se inserte un registro, o actualice, pueden ocurrir divisiones de página, por no haber lugar para los datos en las páginas existentes, y si se produjeran demasiadas divisiones de página, esto podría decrementar el rendimiento de SQL Server. Esto se explica en el siguiente fragmento del artículo:

 “Cuando SQL Server crea un índice, coloca el índice en páginas físicas contiguas, las cuales permiten un óptimo acceso de E/S porque los datos pueden ser leídos secuencialmente. Pero como la tabla crece y cambia con los INSERTS, UPDATES y DELETES, ocurre la división de páginas. Cuando las páginas se dividen, SQL Server debe alojar nuevas páginas en otras partes del disco, y esas nuevas páginas no son contiguas con las páginas físicas originales. Por esto, para cualquier E/S,  accesos E/S no secuenciales deben ser usados para unir los datos, lo cual es mucho más lento, para acceder a los índices de las páginas.”

Sin embargo, un bajo factor de relleno, incrementará el número de páginas que tendrá que leer SQL Server durante las consultas, lo cual también reduce el rendimiento. Esto se explicita en el siguiente fragmento del artículo:

“Como las páginas de datos son movidas desde el disco hasta el buffer, la página entera (incluidos los espacios vacíos) son movidos al buffer. Así que cuanto más bajo es el factor de relleno, más páginas deben ser movidas al buffer de SQL Server, lo que significa que hay menos lugar para otras páginas de datos importantes que pueden ocupar el mismo lugar en ese mismo momento, lo cual reduce el rendimiento.”

Por ello, es que concluimos en que encontrar el factor de relleno más conveniente para un determinado caso, dependerá de un análisis del uso que se le da a la base de datos, siendo recomendables mayores factores de relleno (entre 80% y 100%) en los casos en que se realicen pocas actualizaciones en las tablas, es decir, mayor cantidad de lecturas que de escrituras, y menores factores de relleno (entre 80% y 50%) en caso contrario.

Idealmente se precisa un factor de relleno que evite la división excesiva de páginas, pero que no incremente en forma desfavorable el tamaño de la base de datos.

En resumen, es conveniente reindexar periódicamente las tablas de una base de datos, más aún si se trata de tablas grandes y actualizadas con gran frecuencia, pues la fragmentación que se va produciendo puede afectar negativamente la E/S de disco, perjudicando el rendimiento.

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

A %d blogueros les gusta esto: