¿Cómo creo tareas de mantenimiento para reconstruir índices en mi instancia de Amazon RDS para SQL Server?

Descripción breve

La fragmentación del índice es un problema crítico que puede provocar problemas de rendimiento en las bases de datos de SQL Server si no se reconstruyen rápidamente. Se recomienda supervisar el nivel de fragmentación, reconstruir y reorganizar los índices con regularidad. La fragmentación del índice se produce como carencias en las páginas de datos y como fragmentación lógica.

Amazon RDS para SQL Server no tiene planes de mantenimiento que creen automáticamente planes o trabajos para reconstruir o reorganizar los índices.

Sin embargo, puedes utilizar uno de los métodos siguientes para reconstruir los índices:

• Crea un trabajo de agente SQL Server para reconstruir los índices y actualizar las estadísticas.
• Utiliza scripts y programaciones para crear manualmente un trabajo de reconstrucción.

Nota: Al reconstruir un índice, el motor de base de datos elimina y vuelve a crear el índice. Según el tipo de índice y la versión del motor de base de datos, puedes crear una operación de reconstrucción sin conexión o en línea. Al reorganizar un índice, el motor de base de datos no elimina ni vuelve a crear el índice. En su lugar, el motor de base de datos reestructura la información de las páginas.

Resolución

Creación de un trabajo de agente SQL Server para reconstruir índices y actualizar las estadísticas

Sigue estos pasos:

1. Inicia el cliente Microsoft SQL Server Management Studio (SSMS) y, a continuación, inicia sesión en él.

2. En el panel derecho, haz clic con el botón secundario en el Agente SQL Server y, a continuación, selecciona Nuevo trabajo.

3. En Nombre, introduce un nombre para el trabajo del agente y, en Descripción, introduce una descripción.

4. Elige Aceptar.

5. Selecciona Pasos y, a continuación, elige Nuevo.

6. En Nombre del paso, introduce un nombre para el paso.

7. Selecciona Base de datos y, a continuación, agrega el comando que desees ejecutar periódicamente.
   A continuación se muestra un ejemplo de comando SQL de reconstrucción de índices:

   Use [DBNAME]SET NOCOUNT ON  
   DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fragment float,@command VARCHAR(4000)  
   DECLARE AWS_Cusrsor CURSOR FOR  
   SELECT A.object_id,A.index_id,QUOTENAME(SS.NAME) AS schemaname,QUOTENAME(OBJECT_NAME(B.object_id,B.database_id))as tablename ,QUOTENAME(A.name) AS ixname,B.avg_fragmentation_in_percent AS avg_fragment FROM sys.indexes A inner join sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') AS B  
   ON A.object_id=B.object_id and A.index_id=B.index_id  
   INNER JOIN SYS.OBJECTS OS ON A.object_id=OS.object_id  
   INNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_id  
   WHERE B.avg_fragmentation_in_percent>30  AND A.index_id>0 AND A.IS_DISABLED<>1  
   ORDER BY tablename,ixname  
   OPEN AWS_Cusrsor  
   FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment  
   WHILE @@FETCH_STATUS=0  
   BEGIN  
   IF @avg_fragment>=30.0  
   BEGIN  
   SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';  
   --Can add following line for index reorganization. Else remove following line.  
   SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REORGANIZE';  
   END  
   --PRINT @command  
   EXEC(@command)  
   FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment  
   END  
   CLOSE AWS_Cusrsor  
   DEALLOCATE AWS_Cusrsor

   Nota: Sustituye DBNAME por el nombre de tu base de datos. Puedes usar el comando de ejemplo anterior para reconstruir los índices fragmentados de todas las tablas de la base de datos especificada que superen el 30 % de fragmentación. Si ejecutas el mismo comando SQL para todas las bases de datos, modifica el comando en consecuencia o crea un trabajo independiente para cada base de datos.

8. Elige Aceptar.

9. Selecciona Programaciones y, a continuación, elige Nuevo para agregar una programación sobre cuándo ejecutar el trabajo de reconstrucción del índice.

10. Introduce los detalles de la programación y, a continuación, selecciona Aceptar.

11. Haz clic con el botón derecho en el trabajo y, a continuación, selecciona Iniciar trabajo por pasos para comprobar que el trabajo se puede ejecutar.

Nota: Antes de desplegar el índice en una base de datos de producción, prueba los scripts del ejemplo anterior en una base de datos de RDS en desarrollo. El tiempo de reconstrucción del índice varía según el tamaño y la cantidad de índices.

Para generar planes de ejecución óptimos, el optimizador debe tener información actualizada sobre la distribución de los valores clave (estadísticas) de las columnas de la tabla. Se recomienda actualizar regularmente las estadísticas de todas las tablas. No actualices las estadísticas los días en que reconstruyas los índices.

Las estadísticas de actualización funcionan en una tabla cada vez. El comando sp_updatestats a nivel de base de datos no está disponible en Amazon RDS. Para obtener más información, consulta sp_updatestats en el sitio web de Microsoft.

Para actualizar las estadísticas de todos los objetos de una base de datos, usa las estadísticas de actualización para escribir un cursor. O bien, crea un contenedor alrededor de sp_updatestats y prográmalo.

Para usar un contenedor alrededor de sp_updatestats, sigue estos pasos:

1. Ejecuta el siguiente comando para crear un procedimiento almacenado:

   create procedure myRDS_updatestatswith execute as ‘dbo’  
   as  
   exec sp_updatestats  
   go

2. Concede permiso de ejecución a un usuario en el nuevo procedimiento:

   grant execute on myRDS_updatestats to user

3. Programa trabajos de actualización de estadísticas.

Uso de scripts y programaciones para crear manualmente trabajos de reconstrucción

Para comprobar los índices fragmentados y ejecutar la reconstrucción del índice a partir de ellos según una programación, crea manualmente scripts o procedimientos. Puedes usar scripts para crear tu propio código y configurar trabajos de mantenimiento manuales.

También puedes usar los scripts SQL aws-rds-indexmaintenance-job-example en el sitio web de GitHub. Los scripts reconstruyen y reorganizan los índices semanalmente según el nivel de fragmentación. Los scripts crean una base de datos (IndexStats) y objetos (tablas) para almacenar información sobre todas las bases de datos de la instancia, incluidas las tablas, los índices y los porcentajes de fragmentación de las bases de datos.

El ejemplo aws-rds-indexmaintenance-job-example contiene los scripts CreateDatabaseAndObjects.sql y CreateWeeklyMaintenanceJob.sql.

El script CreateDatabaseAndObjects.sql crea las tablas siguientes:

• Una tabla de mensajes que contiene el mensaje para la acción REBUILD o REORGANIZE que se produce en el índice.
  Nota: Si es necesario, puedes copiar y ejecutar manualmente el mensaje.
• Una tabla ServerDatabases que registra las bases de datos de la instancia actual.
  Nota: La tabla excluye las bases de datos del sistema (Master, Model, TempDB y msdb). La tabla también excluye las bases de datos que crean los componentes de SQL Server, como SSIS y SSRS (rdsadmin_ReportServer y rdsadmin_ReportServerTempDB).
• Una tabla ServerTables que recopila tablas de todas las bases de datos de la tabla ServerDatabases.

El script CreateWeeklyMaintenanceJob.sql crea los siguientes procedimientos almacenados:

• El procedimiento sp_PopulateDatabases registra todas las bases de datos de la instancia en la tabla ServerDatabases.
  Nota: La tabla excluye las bases de datos del sistema o las bases de datos que crean los componentes de SQL Server, como SSAS y SSRS. La tabla incluye SSIDB para SSIS.
• El procedimiento sp_PopulateTables registra las tablas de cada base de datos en ServerTables.
  Nota: Una vez que el procedimiento registra las tablas, comprueba el esquema al que pertenece la tabla y, a continuación, busca los índices que tiene el esquema. A continuación, el procedimiento busca en los índices la información de índice más fragmentada y la registra.
• El procedimiento sp_ReindexTables lee la información de ServerTables y usa las siguientes reglas para iniciar el proceso de reconstrucción o desfragmentación:
  La fragmentación es del 0 al 9 % = NADA
  La fragmentación es del 10 al 30 % = REORGANIZAR
  La fragmentación es del 31 al 100 % = RECONSTRUIR

Para usar los scripts aws-rds-indexmaintenance-job-example de GitHub, sigue estos pasos:

1. Copia y ejecuta el script CreateDatabaseAndObjects.sql.
2. Ejecuta el script CreateWeeklyMaintenanceJob.sql.

Nota: Debido a las restricciones de los servicios administrados, puedes crear trabajos de agente SQL Server solo en una cuenta de AWS que haya iniciado sesión en el cliente de SSMS.