¿Cómo creo tareas de mantenimiento para reconstruir índices en mi instancia de 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 y 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.

Nota: La reconstrucción de un índice se descarta y, a continuación, se vuelve a crear el índice. Según el tipo de índice y la versión del motor de base de datos, la operación de reconstrucción se puede realizar sin conexión o en línea. La reorganización reestructura la información de las páginas en lugar de eliminar y volver a crear los índices.

RDS para SQL Server no tiene planes de mantenimiento que creen automáticamente planes y trabajos para reconstruir o reorganizar los índices. Sin embargo, puede utilizar los métodos siguientes para reconstruir los índices:

• Método 1: Cree un trabajo de agente SQL Server para reconstruir los índices y actualizar las estadísticas.
• Método 2: Cree manualmente el trabajo de reconstrucción mediante scripts y programe según sea necesario.

Resolución

Método 1: Crear un trabajo de agente SQL Server para reconstruir índices y actualizar las estadísticas

1. Inicie el cliente Microsoft SQL Server Management Studio y, a continuación, inicie sesión en él.

2. En el panel derecho, haga clic con el botón derecho en Agente SQL Server. A continuación, elija Nuevo, Trabajo para crear un trabajo de agente SQL.

3. Introduzca un nombre y una descripción para el trabajo de agente y, a continuación, seleccione OK.

Ejemplo:

• Nombre: Indexrebuild_job
• Descripción: Trabajo de RDS para agente SQL Server para la reconstrucción de índices.

4. Seleccione Pasos y, a continuación, seleccione Nuevo para añadir un paso de ejecución. Aparece una nueva ventana.

5. Introduzca el nombre del paso.

6. Seleccione Base de datos y, a continuación, añada el comando que desee ejecutar periódicamente.

A continuación se muestra un ejemplo de comando SQL de reconstrucción de índices. Puede usar este comando de ejemplo para reconstruir los índices fragmentados de todas las tablas de la base de datos especificada que superen el 30 % de fragmentación. Cambie el valor de [DBNAME] en la primera línea por el nombre correcto de la base de datos. Si ejecuta el mismo comando SQL para todas las bases de datos, modifique el comando en consecuencia o cree un trabajo independiente para cada base de datos.

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

7. Seleccione OK.

8. Seleccione Programas y, a continuación, elija Nuevo para añadir un programa sobre cuándo ejecutar el trabajo de reconstrucción del índice.

9. Introduzca el Nombre del programa, el Tipo de programa, y campos similares y, a continuación, seleccione OK.

10. Vea el trabajo que acaba de crear y haga clic con el botón derecho en él. A continuación, seleccione Iniciar trabajo en el paso para ejecutar el trabajo manualmente y comprobar que el trabajo se puede ejecutar correctamente.

Nota: Pruebe los scripts proporcionados en este ejemplo en una base de datos de RDS de desarrollo antes de desplegarlos en una base de datos de RDS de producción. El tiempo de reconstrucción del índice varía considerablemente según el tamaño y la cantidad de índices.

El optimizador debe tener información actualizada sobre la distribución de los valores clave (estadísticas) de las columnas de la tabla para generar planes de ejecución óptimos. Se recomienda actualizar las estadísticas de todas las tablas de forma regular. Evite actualizar las estadísticas los días en que reconstruya los índices.

Tenga en cuenta que las estadísticas de actualización funcionan en una tabla cada vez. El comando de nivel de base de datos sp_updatestats (en el sitio web de Microsoft) no está disponible en Amazon RDS. Escriba un cursor utilizando las estadísticas de actualización para actualizar las estadísticas de todos los objetos de una base de datos. O bien, cree un contenedor alrededor de sp_updatestats y prográmelo.

Para usar un contenedor alrededor de sp_updatestats, haga lo siguiente:

1. Ejecute el comando para crear un procedimiento almacenado:

create procedure myRDS_updatestats
with execute as ‘dbo’
as
exec sp_updatestats
go

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

grant execute on myRDS_updatestats to user

3. Siga los pasos anteriores del Método 1 para programar los trabajos de actualización de estadísticas.

Método 2: Crear manualmente trabajos de reconstrucción mediante scripts y programas según sea necesario

Puede crear manualmente scripts o procedimientos para comprobar los índices fragmentados y ejecutar la reconstrucción del índice a partir de ellos según un programa. Puede crear su propio código y configurar trabajos de mantenimiento manuales mediante scripts.

También puede usar los scripts SQL aws-rds-indexmaintenance-job-example de GitHub. Estos scripts reconstruyen y reorganizan los índices semanalmente según el nivel de fragmentación. El script crea una base de datos (IndexStats) y objetos (tablas) para almacenar información sobre todas las bases de datos de la instancia. Esta información incluye las tablas, los índices y los porcentajes de fragmentación de las bases de datos.

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

El script CreateDatabaseAndObjects.sql hace lo siguiente:

• Crea una tabla denominada ServerDatabases. En esta tabla se registran las bases de datos de la instancia actual. Se excluyen las bases de datos del sistema (Master, Model, TempDB y msdb). También se excluyen las bases de datos creadas por componentes de SQL Server, como SSIS y SSRS (rdsadmin_ReportServer, rdsadmin_ReportServerTempDB).
• Crea una tabla denominada ServerTables. Esta tabla recopila las tablas de todas las bases de datos de la tabla ServerDatabases.
• Crea una tabla denominada Messages. Esta tabla contiene el mensaje sobre el que se actuó en el índice (RECONSTRUIR o REORGANIZAR). Puede copiar el mensaje y ejecutarlo manualmente, si es necesario.

El script CreateWeeklyMaintenanceJob.sql crea el siguiente procedimiento almacenado:

• sp_PopulateDatabases: Este procedimiento recorre todas las bases de datos de la instancia y las registra en la tabla ServerDatabases. No incluye las bases de datos del sistema ni las bases de datos creadas por componentes de SQL Server, como SSAS y SSRS. Se incluye SSIDB para SSIS.
• sp_PopulateTables: Este procedimiento recorre cada base de datos y registra sus tablas en ServerTables. Tras registrar las tablas, comprueba el esquema al que pertenece la tabla y, a continuación, busca los índices que tenga. El procedimiento almacenado examina los índices, busca la información del índice más fragmentada y la registra.
• **Sp_ReindexTables:**Este procedimiento lee la información de ServerTables e inicia el proceso de reconstrucción o desfragmentación mediante las siguientes reglas:
  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, copie y ejecute primero el archivo CreateDatabaseAndObjects.sql. A continuación, ejecute el script CreateWeeklyMaintenanceJob.sql.

**Nota:**Debido a las restricciones de los servicios administrados, los trabajos del agente SQL Server solo se pueden crear con la cuenta que haya iniciado sesión actualmente. No se permiten otras cuentas como propietarias de trabajos.