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

Última actualización: 08-12-2022

Quiero crear tareas de mantenimiento de reconstrucción de índices en mi instancia de Amazon Relational Database Service (Amazon RDS) para Microsoft SQL Server. ¿Cómo lo hago?

Descripción breve

La fragmentación de índices es un problema crítico, ya 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 de los índices se produce como carencias en las páginas de datos y una fragmentación lógica.

Nota: Al reconstruir un índice, se elimina y se vuelve a crear el índice. Según el tipo de índice y la versión del motor de base de datos, se puede realizar una operación de reconstrucción sin conexión o en línea. Reorganizar 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 siguientes métodos para reconstruir los índices:

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

Resolución

Método 1: Crear un trabajo de agente de SQL Server para reconstruir los í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 SQL Server Agent (Agente de SQL Server). A continuación, elija New (Nuevo), Job (Trabajo) para crear un trabajo de agente de SQL.

3.    Introduzca un Name (Nombre) y unaDescription (Descripción) para el trabajo de agente y, a continuación, seleccione OK (Aceptar).

Ejemplo:

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

4.    Seleccione Steps (Pasos) y, a continuación, seleccione New (Nuevo) para agregar un paso de ejecución. Aparece una nueva ventana.

5.    Introduzca el Step name (Nombre del paso).

6.    Seleccione Database (Base de datos) y, a continuación, agregue el comando que desee ejecutar periódicamente.

A continuación, se muestra un ejemplo de comando SQL de reconstrucción de índices. Puede utilizar 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] de la primera línea por el nombre correcto para su 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 (Aceptar).

8.    Seleccione Schedules (Programación) y, a continuación, elija New (Nuevo) para agregar una programación que indique cuándo ejecutar el trabajo de reconstrucción del índice.

9.    Ingrese el nombre de la programación en Name (Nombre), el tipo de programación en Schedule type (Tipo de programación), etc. y, a continuación, seleccione OK (Aceptar).

10.    Visualice el trabajo que acaba de crear y haga clic derecho. A continuación, seleccione Stat Job at Step (Iniciar trabajo en el paso) para ejecutar el trabajo de forma manual y comprobar que se puede ejecutar correctamente.

Nota: Pruebe los scripts proporcionados en este ejemplo en una base de datos RDS de desarrollo antes de implementarlos 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 el número 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 con regularidad. Evite actualizar las estadísticas los días en que esté reconstruyendo los índices.

Tenga en cuenta que las estadísticas de actualización funcionan en una tabla a la vez. El comando de nivel de base de datos sp_updatestats 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 las tareas de actualización de estadísticas.

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

Puede crear scripts o procedimientos manualmente para comprobar los índices fragmentados y ejecutar la reconstrucción del índice contra ellos en una programación. Puede crear su propio código y configurar tareas de mantenimiento manual 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 aws-rds-indexmaintenance-job-example contiene dos scripts,CreateDatabaseAndObjects.sql yCreateWeeklyMaintenanceJob.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 recopila tablas de todas las bases de datos de la tabla ServerDatabases.
  • Crea una tabla denominada Messages (Mensajes). 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 revisa todas las bases de datos de la instancia y las registra en la tabla ServerDatabases. No incluye bases de datos del sistema ni bases de datos creadas por componentes de SQL Server, como SSAS y SSRS. Se incluye el SSIDB para el SSIS.
  • sp_PopulateTables: este procedimiento examina 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 revisa los índices y 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, primero copie y ejecute CreateDatabaseAndObjects.sql. A continuación, ejecute el script CreateWeeklyMaintenanceJob.sql.

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


¿Le resultó útil este artículo?


¿Necesita asistencia técnica o con la facturación?