Desfragmentación de índices en bases de datos de Project Server 2007

Actualizado: septiembre de 2008

 

Última modificación del tema: 2015-02-27

Las tareas de mantenimiento de las bases de datos se pueden realizar mediante comandos de Transact-SQL o mediante el asistente para mantenimiento de bases de datos. En este artículo se ofrecen detalles acerca de ambos procedimientos.

Las tareas de mantenimiento de bases de datos recomendadas para las bases de datos de Microsoft Office Project Server 2007 son:

• Comprobación de la integridad de la base de datos

• Desfragmentación de índices mediante su reorganización o regeneración

• Configuración del factor de relleno para un servidor

• Supervisión del tamaño de las bases de datos para ampliar previamente o para reducir las bases de datos

• Limpieza del historial

• Actualización de las estadísticas

Desfragmentación de índices mediante su reorganización o regeneración

La fragmentación se produce cuando la asignación de almacenamiento lógico y físico de una base de datos contiene muchas áreas de almacenamiento dispersas que son insuficientes, no están físicamente contiguas o están demasiado fragmentadas para usarlas de manera eficaz. La fragmentación puede ser el resultado de muchas inserciones, actualizaciones o eliminaciones de una tabla. Cuando se fragmenta una tabla, los índices definidos en la tabla también se fragmentan.

Office Project Server 2007 usa tipos de GUID como claves de agrupación en clústeres. Esto evita que varias inserciones simultáneas compitan por las mismas páginas de datos (zonas activas de inserción) pero, a la vez, puede producir la fragmentación de tablas e índices. La fragmentación puede producirse porque los registros nuevos se pueden insertar en cualquier lugar en el árbol b, en lugar de al final, lo que produce una mayor tendencia a dividir la página (índice y datos) y, por lo tanto, fragmentarla. Para mitigarlo, se agrupan en clúster las claves compuestas que usan el UID de proyecto, de modo que se garantiza que las páginas de datos contendrán datos relacionados, aunque la desfragmentación regular de las tablas grandes mejorará el rendimiento, especialmente en grandes implementaciones de Office Project Server 2007.

Con el tiempo, la fragmentación de la base de datos puede provocar una reducción del rendimiento (actividad de disco innecesaria) y un uso poco eficaz del espacio. Para mitigar la fragmentación y reducir la frecuencia con que se produce la fragmentación, establezca manualmente el mayor tamaño posible para las bases de datos de contenido en función de sus requisitos empresariales y de la arquitectura de las bases de datos. Por ejemplo, si tiene un requisito por el que debe limitar las bases de datos de contenido a 100 gigabytes (GB), una vez creadas las bases de datos de contenido establezca su tamaño en 100 GB en SQL Server Management Studio.

Aunque las tablas pueden desfragmentarse, la desfragmentación de índices ofrece más ventajas para el rendimiento de las bases de datos y es mucho más rápida. En este artículo sólo se describe cómo desfragmentar los índices.

Antes de implementar un plan de mantenimiento de fragmentación de bases de datos, determine qué tablas e índices están más fragmentados y cree un plan de mantenimiento para regenerar o reorganizar los índices.

La medición de la fragmentación puede realizarse de la siguiente manera:

• En SQL Server 2005, use la vista de administración dinámica sys.dm_db_index_physical_stats

• En SQL Server 2000, use DBCC SHOWCONTIG

Tenga en cuenta que el algoritmo para calcular la fragmentación es más preciso en sys.dm_db_index_physical_stats que en DBCC SHOWCONTIG. Como consecuencia, los valores de fragmentación calculados por sys.dm_db_index_physical_stats son mayores.

Medición de la fragmentación con sys.dm_db_index_physical_stats (SQL Server 2005)

En SQL Server 2005, use la vista de administración dinámica sys.dm_db_index_physical_stats para determinar la fragmentación de los índices en una tabla o vista especificada.

Para medir la fragmentación, se recomienda que supervise la columna avg_fragmentation_in_percent. El valor de avg_fragmentation_in_percent debe ser lo más próximo posible a cero para obtener el máximo rendimiento. No obstante, pueden aceptarse valores de 0% a 10%.

Para obtener más información acerca de cómo usar sys.dm_db_index_physical_stats, vea el tema sobre sys.dm_db_index_physical_stats (https://go.microsoft.com/fwlink/?linkid=128479\&clcid=0xC0A)

Medición de la fragmentación con DBCC SHOWCONTIG (SQL Server 2000)

Para comprobar la fragmentación de las tablas de base de datos, un administrador puede usar la función DBCC SHOWCONTIG para crear un informe de fragmentación lógica y de exploración de extensión. Para obtener una explicación completa acerca de los resultados de DBCC SHOWCONTIG, vea el tema sobre DBCC SHOWCONTIG (en inglés) (https://go.microsoft.com/fwlink/?linkid=110841\&clcid=0xC0A) (en inglés).

Para medir la fragmentación, se recomienda que supervise el valor de densidad de recorrido devuelto por DBCC SHOWCONTIG. En las tablas en las que todo el contenido está contiguo, la densidad de recorrido es 100.

Reducción de la fragmentación de una base de datos

Para reducir el nivel de fragmentación del índice, ejecute el procedimiento almacenado descrito en el tema sobre cómo desfragmentar bases de datos de Windows SharePoint Services 3.0 y bases de datos de SharePoint Server 2007 (https://go.microsoft.com/fwlink/?linkid=110843\&clcid=0xC0A) en Microsoft Knowledge Base.

Después de determinar el nivel de fragmentación de las bases de datos, puede programar el procedimiento almacenado para que se ejecute diariamente, semanalmente o mensualmente, en función de sus necesidades y de la velocidad de cambio general de su entorno. Por lo general, se recomienda establecer como mínimo una programación semanal de desfragmentación. Asimismo, se recomienda programar las operaciones de desfragmentación después de ejecutar las operaciones de DBCC CHECKDB REPAIR.

Este procedimiento almacenado cambia los índices de las bases de datos de contenido. No se permite realizar ninguna modificación al procedimiento almacenado. Para obtener más información acerca de los cambios compatibles con las bases de datos de contenido de Productos y Tecnologías de SharePoint, vea el tema sobre compatibilidad de cambios en las bases de datos usadas por productos de Office Server y por Windows SharePoint Services (https://go.microsoft.com/fwlink/?linkid=110844\&clcid=0xC0A), en Microsoft Knowledge Base.

Reducción de la fragmentación de una tabla específica y sus índices

Si desea desfragmentar el índice que está asociado a una tabla determinada en lugar de a una base de datos completa, puede reorganizar o regenerar el índice. Para obtener más información, vea el tema sobre las estructuras de índices clúster (en inglés) (https://go.microsoft.com/fwlink/?linkid=128480\&clcid=0xC0A) (en inglés).

Reorganizar un índice significa reorganizar el nivel hoja del índice. La reorganización de índices desfragmenta y compacta los índices clúster y los índices no agrupados de las tablas y las vistas, y puede mejorar significativamente el rendimiento de recorrido del índice. La reorganización siempre se realiza con conexión para que la tabla subyacente esté disponible para los usuarios. La reorganización equivale a la instrucción DBCC INDEXDEFRAG de SQL Server 2000.

Regenerar un índice significa que el índice se volverá a generar con las mismas columnas, tipo de índice, atributo de exclusividad y criterio de ordenación. La regeneraci´no mejora el rendimiento de los recorridos y búsquedas de índice. El índice se puede regenerar con una tabla con o sin conexión. La regeneración equivale a la instrucción DBCC DBREINDEX de SQL Server 2000.

El nivel de fragmentación de un índice determina el método que se debe usar para desfragmentarlo y si puede permanecer con conexión o se debe poner sin conexión.

Nivel de fragmentación	Método de desfragmentación
Hasta 10%	Reorganizar (con conexión)
Entre 10% y 75%	Regenerar (con conexión)
Más de 75%	Regenerar (sin conexión)

Tenga en cuenta que no se admite el uso de los comandos DROP INDEX y CREATE INDEX en las bases de datos de Productos y Tecnologías de SharePoint.

Los índices se pueden reorganizar y regenerar mediante la instrucción ALTER INDEX de SQL Server 2005, el Asistente para mantenimiento de SQL Server 2005, las instrucciones DBCC INDEXDEFRAG y DBCC DBREINDEX de SQL Server 2000 o el Asistente para mantenimiento de SQL Server 2000. En este tema sólo se describen detalladamente las opciones de SQL Server 2005. Para obtener más información acerca de las opciones de SQL Server 2000, vea los recursos siguientes:

• DBCC INDEXDEFRAG (en inglés) (https://go.microsoft.com/fwlink/?linkid=128482\&clcid=0xC0A) (en inglés)

• DBCC DBREINDEX (en inglés) (https://go.microsoft.com/fwlink/?linkid=128483\&clcid=0xC0A) (en inglés)

• Asistente para el plan de mantenimiento de bases de datos (en inglés) (https://go.microsoft.com/fwlink/?linkid=110849\&clcid=0xC0A) (en inglés)

Uso de ALTER INDEX

ALTER INDEX permite a un administrador de la base de datos realizar operaciones de mantenimiento en un índice de tabla o vista existente. Se puede usar para deshabilitar, regenerar y reorganizar los índices u, opcionalmente, las opciones de configuración del índice. ALTER INDEX reemplaza las instrucciones DBCC DBREINDEX y DBCC INDEXDEFRAG.

En la mayoría de los casos, los índices se pueden regenerar mientras la base de datos está conectada porque no hay mejoras importantes cuando se regeneran los índices sin conexión. Sin embargo, es importante tener en cuenta que cuando se regenera un índice, se coloca un bloqueo de tabla compartida en la tabla, que impide realizar cualquier operación excepto las operaciones SELECT. Las bases de datos de Productos y Tecnologías de SharePoint usan específicamente índices clúster. Cuando se regenera un índice clúster, se coloca un bloqueo de tabla exclusiva en la tabla, que impide que los usuarios finales tengan acceso a la misma.

Puede personalizar el script de ejemplo siguiente para regenerar todos los índices de una tabla.

USE Contoso_Content_1
GO
ALTER INDEX ALL ON [database_name. [ schema_name ] . | schema_name. ]table_or_view_name
REBUILD WITH (FILLFACTOR = 70, SORT_IN_TEMPDB = ON, ONLINE = ON,
STATISTICS_NORECOMPUTE = ON)
GO

Consideraciones especiales para la base de datos de informes

Como se espera que los clientes implementen informes personalizados basados en campos personalizados y en datos disponibles en la base de datos de informes, se aconseja realizar los procedimientos recomendados siguientes para la escritura de T-SQL y la creación de índices con el fin de garantizar una solución de informes escalable y eficaz. Office Project Server 2007 no indiza estas tablas (generadas dinámicamente) fuera de la clave principal. La Actualización de infraestructura para servidores de Microsoft Office proporciona funcionalidad adicional. Para obtener más información, vea la sección sobre optimizaciones de RDS para campos personalizados en el artículo descargable sobre el lanzamiento de la actualización de infraestructura de Project 2007 para servidor y cliente (en inglés) (https://go.microsoft.com/fwlink/?linkid=121912\&clcid=0xC0A) (en inglés).

Cuando se trabaja con el soporte técnico y el servicio al cliente de Microsoft, es posible que un ingeniero de soporte técnico le pida que quite los índices adicionales creados o que quite las columnas adicionales agregadas a los índices existentes. Esto es debido a que los índices adicionales pueden cambiar las rutas de acceso a los datos y, en algunos casos, provocar un rendimiento inesperado y problemas de bloqueo y desbloqueo.

Configuración del factor de relleno para un servidor

El factor de relleno sirve para mejorar aún más el almacenamiento y el rendimiento de los datos de índice. Cuando los índices se crean o se regeneran, el factor de relleno (de 1 a 100) determina el porcentaje de espacio de cada página de nivel hoja que se puede rellenar con datos. El espacio restante se reserva para un futuro crecimiento. En muchas ocasiones, el valor predeterminado 0 del nivel de factor de relleno de todo el servidor resulta adecuado. Sin embargo, en Microsoft Office SharePoint Server 2007, el valor adecuado para todo el servidor es 70, para permitir el crecimiento y reducir la fragmentación.

Aunque es posible, no se recomienda establecer el factor de relleno de cada una de las tablas o índices.

Para ver el valor del factor de relleno de algunos índices, consulte la vista de catálogo sys.indexes. Para obtener más información acerca de la vista, vea el tema sobre valores de sys.index (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=128510\&clcid=0xC0A).

Para configurar el valor del factor de relleno de todo el servidor, use el procedimiento almacenado del sistema sp_configure. Para obtener más información, vea el tema sobre sp_configure (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=128512\&clcid=0xC0A).