Comment créer des tâches de maintenance pour reconstruire des index dans mon instance Amazon RDS for SQL Server ?

Brève description

La fragmentation des index est un problème critique qui peut réduire les performances dans les bases de données SQL Server si elles ne sont pas régénérées rapidement. Il est recommandé de surveiller le niveau de fragmentation, de reconstruire et de réorganiser régulièrement les index. La fragmentation des index prend la forme d’intervalles dans les pages de données et la fragmentation logique.

RDS for SQL Server ne dispose pas de plans de maintenance capables de créer automatiquement des plans et des tâches pour reconstruire ou réorganiser les index.

Cependant, vous pouvez utiliser l’une des méthodes suivantes pour reconstruire vos index :

• Créez une tâche d'agent SQL Server pour reconstruire les index et mettre à jour les statistiques.
• Utilisez des scripts et des planifications pour créer manuellement une tâche de reconstruction.

Remarque : Lorsque vous reconstruisez un index, le moteur de base de données arrête et recrée l'index. Selon le type d'index et la version du moteur de base de données, vous pouvez créer une opération de reconstruction hors ligne ou en ligne. Lorsque vous réorganisez un index, le moteur de base de données ne supprime ni ne recrée l'index. À la place, il restructure les informations figurant sur les pages.

Résolution

Créer une tâche d'agent SQL Server pour reconstruire les index et mettre à jour les statistiques

Procédez comme suit :

1. Démarrez le client Microsoft SQL Server Management Studio (SSMS), puis connectez-vous à celui-ci.

2. Dans le volet droit, cliquez avec le bouton droit sur Agent SQL Server, puis choisissez Nouvelle tâche.

3. Dans Nom, entrez un nom pour la tâche d'agent, et dans Description, entrez une description.

4. Cliquez sur OK.

5. Choisissez Étapes, puis choisissez Nouveau.

6. Dans Nom de l'étape, entrez un nom pour l'étape.

7. Choisissez Base de données, puis ajoutez la commande que vous souhaitez exécuter régulièrement.
   Voici un exemple de commande SQL de reconstruction d'index :

   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

   Remarque : Remplacez DBNAME par le nom de votre base de données. Vous pouvez utiliser cet exemple de commande pour reconstruire les index fragmentés de toutes les tables de la base de données spécifiée dont le taux de fragmentation dépasse 30 %. Si vous exécutez la même commande SQL pour toutes les bases de données, modifiez-la en conséquence ou créez une tâche distincte pour chaque base de données.

8. Cliquez sur OK.

9. Sélectionnez Planification, puis choisissez Nouveau pour ajouter une planification indiquant quand exécuter la tâche de reconstruction de l'index.

10. Entrez les détails de votre planification, puis cliquez sur OK.

11. Cliquez avec le bouton droit sur la tâche, puis choisissez Démarrer la tâche à l'étape pour vérifier que la tâche peut être exécutée.

Remarque : Avant de déployer l'index dans une base de données de production, testez les scripts de l'exemple précédent dans une base de données RDS de développement. Le temps nécessaire à la reconstruction de l'index varie considérablement en fonction de la taille et de la quantité d'index.

Les informations sur la distribution des valeurs clés (statistiques) des colonnes de la table doivent être à jour pour que l'optimiseur puisse générer des plans d'exécution optimaux. Il est recommandé de mettre régulièrement à jour les statistiques de toutes les tables. Évitez de mettre à jour les statistiques les jours où vous reconstruisez les index.

Les statistiques de mise à jour fonctionnent sur une seule table à la fois. La commande sp_updatestats au niveau de la base de données n'est pas disponible dans Amazon RDS. Pour plus d'informations, consultez la page sp_updatestats sur le site Web de Microsoft.

Pour mettre à jour les statistiques sur tous les objets d'une base de données, écrivez un curseur à l'aide des statistiques de mise à jour. Vous pouvez également créer un wrapper autour de sp_updatestats et le planifier.

Pour utiliser un wrapper autour de sp_updatestats, procédez comme suit :

1. Exécutez la commande suivante pour créer une procédure stockée :

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

2. Accordez l'autorisation d'exécution à un utilisateur sur la nouvelle procédure :

   grant execute on myRDS_updatestats to user

3. Planifiez les tâches de mise à jour et de démarrage.

Utiliser des scripts et des planifications pour créer manuellement des tâches de reconstruction

Pour vérifier les index fragmentés et exécuter une reconstruction de l'index en conséquence, créez manuellement des scripts ou des procédures. Vous pouvez créer votre propre code et configurer des tâches de maintenance manuelle à l'aide de scripts.

Vous pouvez également utiliser les scripts SQL aws-rds-indexmaintenance-job-example disponibles sur le site Web de GitHub. Ces scripts reconstruisent et réorganisent les index chaque semaine en fonction du niveau de fragmentation. Les scripts créent une base de données (IndexStats) et des objets (tables) pour stocker des informations sur toutes les bases de données de l'instance, y compris les tables, les index et les pourcentages de fragmentation des bases de données.

L'exemple aws-rds-indexmaintenance-job-example contient les scripts CreateDatabaseAndObjects.sql et CreateWeeklyMaintenanceJob.sql.

Le script CreateDatabaseAndObjects.sql crée les tables suivantes :

• Une table Messages contenant le message correspondant à l'action REBUILD ou REORGANIZE qui se produit sur l'index.
  Remarque : Vous pouvez copier et exécuter manuellement le message, si nécessaire.
• Une table ServerDatabases qui enregistre les bases de données de l'instance actuelle.
  Remarque : La table exclut les bases de données système (Master, Model, TempDB et msdb). La table exclut également les bases de données créées par les composants SQL Server, telles que SSIS et SSRS (rdsadmin_ReportServer et rdsadmin_ReportServerTempDB).
• Une table ServerTables qui collecte les tables de toutes les bases de données de la table ServerDatabases.

Le script CreateWeeklyMaintenanceJob.sql crée la procédure stockée suivante :

• La procédure sp_PopulateDatabases enregistre toutes les bases de données de l'instance dans la table ServerDatabases.
  Remarque : La table exclut les bases de données système ou les bases de données créées par les composants SQL Server, telles que SSAS et SSRS. La table inclut SSIDB pour SSIS.
• La procédure sp_PopulateTables enregistre les tables de chaque base de données dans ServerTables.
  Remarque : Une fois que la procédure a enregistré les tables, elle vérifie le schéma auquel appartient la table, puis recherche les index du schéma. La procédure recherche ensuite dans les index les informations d'index les plus fragmentées et les enregistre.
• La procédure sp_ReindexTables lit les informations de ServerTables et utilise les règles suivantes pour lancer le processus de reconstruction ou de défragmentation :
  La fragmentation est de 0 à 9 % = R.A.S.
  La fragmentation est de 10 à 30 % = RÉORGANISER
  La fragmentation est de 31 à 100 % = RECONSTRUIRE

Pour utiliser les scripts aws-rds-indexmaintenance-job-example scripts de GitHub, procédez comme suit :

1. Copiez et exécutez le script CreateDatabaseAndObjects.sql.
2. Exécutez le script CreateWeeklyMaintenanceJob.sql.

Remarque : En raison des restrictions liées aux services gérés, vous ne pouvez créer des tâches de l’agent SQL Server que dans un compte AWS connecté au client SSMS.