Comment créer des tâches de maintenance pour régénérer des index dans mon instance RDS pour 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 et de régénérer et réorganiser régulièrement les index. La fragmentation des index prend la forme de lacunes dans les pages de données et la fragmentation logique.

**Remarque :**La régénération d'un index supprime puis recrée cet index. Selon le type d'index et la version du moteur de base de données, il est possible d'effectuer une opération de régénération hors ligne ou en ligne. La fonctionnalité Réorganiser restructure les informations des pages au lieu de supprimer puis de recréer les index.

RDS pour SQL Server ne dispose pas de plans de maintenance capables de créer automatiquement des plans et des tâches pour régénérer ou réorganiser les index. Cependant, voici des méthodes permettant de régénérer vos index :

• Méthode 1 : Créez une tâche d'agent SQL Server pour régénérer les index et mettre à jour les statistiques.
• Méthode 2 : Créez manuellement une tâche de régénération à l'aide de scripts et planifiez-la selon vos besoins.

Solution

Méthode 1 : Création d'une tâche d'agent SQL Server pour régénérer les index et mettre à jour les statistiques

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

2.    Dans le panneau de droite, cliquez avec le bouton droit sur SQL Server Agent. Choisissez ensuite Nouveau, puis Tâche pour créer une tâche d'agent SQL.

3.    Entrez un nom et une description pour la tâche de l'agent, puis sélectionnez OK.

Exemple :

• **Nom :**Indexrebuild \ _job
• Description : Tâche d'agent du serveur RDS pour SQL pour la régénération de l'index.

4.    Sélectionnez Étapes, puis sélectionnez Nouveau pour ajouter une étape d'exécution. Une nouvelle fenêtre s'affiche.

5.    Entrez le nom de l'étape.

6.    Sélectionnez Base de données, puis ajoutez la commande que vous souhaitez exécuter régulièrement.

Voici un exemple de commande SQL de régénération d'index. Vous pouvez utiliser cet exemple de commande pour régénérer les index fragmentés de toutes les tables dont le taux de fragmentation dépasse 30 % dans la base de données indiquée. Dans la première ligne, remplacez la valeur de [DBNAME] par le nom correct de votre base de données. 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.

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.    Sélectionnez OK.

8.    Sélectionnez Planification, puis choisissez Nouveau pour ajouter un calendrier indiquant quand exécuter la tâche de régénération de l'index.

9.    Remplissez le nom et le type de planification ainsi que les champs similaires, puis sélectionnez OK.

10.    Affichez la tâche que vous venez de créer et cliquez dessus avec le bouton droit de la souris. Choisissez ensuite Start Job at Step pour exécuter manuellement la tâche afin de vérifier son exécution correcte.

**Remarque :**Testez les scripts fournis dans cet exemple dans une base de données RDS de développement avant de les déployer dans une base de données RDS de production. Le temps nécessaire à la régénération 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 régénérez les index.

N'oubliez pas que les statistiques de mise à jour fonctionnent sur une seule table à la fois. La commande au niveau de la base de données sp_updatestats (sur le site Web de Microsoft) n'est pas disponible dans Amazon RDS. Écrivez un curseur à l'aide des statistiques de mise à jour pour mettre à jour les statistiques sur tous les objets d'une base de données. Vous pouvez également créer un wrapper autour de sp_updatestats et le planifier.

Voici comment utiliser un wrapper autour de sp_updatestats :

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

create procedure myRDS_updatestats
with 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.    Suivez les étapes précédentes de la méthode 1 pour planifier des tâches de mise à jour des statistiques.

Méthode 2 : Créez manuellement des tâches de régénération à l'aide de scripts et planifiez-les selon vos besoins

Vous pouvez créer manuellement des scripts ou des procédures pour planifier la vérification des index fragmentés et exécuter une régénération de l'index en conséquence. 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 GitHub. Ces scripts régénèrent et réorganisent les index chaque semaine en fonction du niveau de fragmentation. Le script crée une base de données (IndexStats) et des objets (tables) pour stocker des informations concernant toutes les bases de données de l'instance. Ces informations incluent les tables, les index et les pourcentages de fragmentation des bases de données.

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

Le script CreateDatabaseAndObjects.sql effectue les opérations suivantes :

• Création d'une table nommée ServerDatabases. Cette table enregistre les bases de données de l'instance en cours. Les bases de données système (Master, Model, TempDB et msdb) sont exclues. Les bases de données créées par des composants SQL Server tels que SSIS et SSRS (rdsadmin_ReportServer, rdsadmin_ReportServerTempDB) sont également exclues.
• Création d'une table nommée ServerTables. Cette table rassemble les tables de toutes les bases de données de la table ServerDatabases.
• Création d'une table nommée Messages. Cette table contient le message qui a été traité sur l'index (REBUILD ou REORGANIZE). Vous pouvez copier le message et l'exécuter manuellement, si nécessaire.

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

• sp_PopulateDatabases : Cette procédure parcourt toutes les bases de données de l'instance et les enregistre dans la table ServerDatabases. Elle n'inclut pas les bases de données système ni les bases de données créées par des composants SQL Server tels que SSAS et SSRS. La base de données SSIDB pour SSIS est incluse.
• sp_PopulateTables : Cette procédure parcourt chaque base de données et enregistre ses tables dans ServerTables. Après avoir enregistré les tables, elle vérifie le schéma auquel la table appartient, puis recherche les index qu'elle contient. La procédure stockée parcourt les index, recherche les informations d'index les plus fragmentées et les enregistre.
• Sp_ReindexTables : Cette procédure lit les informations des ServerTables et lance le processus de régénération ou de défragmentation en utilisant les règles suivantes :
  La fragmentation est de 0 à 9 % = R.A.S.
  La fragmentation est de 10 à 30 % = RÉORGANISER
  La fragmentation est de 31 à 100 % = RÉGÉNÉRER

Pour utiliser les scripts aws-rds-indexmaintenance-job-example de GitHub, commencez par copier et exécuter le fichier CreateDatabaseAndObjects.sql. Ensuite, exécutez le script CreateWeeklyMaintenanceJob.sql.

Remarque : En raison des restrictions relatives aux services gérés, seul le compte en cours de connexion peut créer des tâches de l'agent SQL Server. Aucun autre compte n'est autorisé à être propriétaire d'une tâche.