Optimisation et maintenance des index dans SQL Server : Guide complet

Optimisation et maintenance des index dans SQL Server : Guide complet

【Lien utile】sqlserver-kit : Outils, scripts et bonnes pratiques pour Microsoft SQL Server

La gestion des index est une composante clé de la performance des bases de données SQL Server. Le projet open-source sqlserver-kit regroupe des scripts utiles, des outils et des bonnes pratiques pour gérer et optimiser les index. Ce guide explique comment utiliser sqlserver-kit pour une maintenance et une optimisation des index efficaces.

1. Fondements de l'optimisation des index : Pourquoi les index sont-ils si importants ?

Les index jouent le rôle d'« accélérateur » des requêtes, en réduisant le temps d'exécution des requêtes jusqu'à plusieurs fois. Cependant, au fil du temps, les index peuvent se fragmenter, entraînant un affichage de performance. sqlserver-kit fournit des outils de diagnostic complets pour identifier les points de blocage.

1.1 Les dangers de la fragmentation des index

  • Délais de requête augmentés : La fragmentation excessive provoque plus d'opérations I/O sur le disque
  • Consommation accrue des ressources : Augmentation de la charge CPU et de la consommation de mémoire
  • Coûts de maintenance plus élevés : Les index non optimisés rallongent les temps de备份 et de restauration

2. Outils de maintenance des index : Fonctionnalités principales de sqlserver-kit

sqlserver-kit intègre la solution Ola Hallengren IndexOptimize, une solution de référence pour la maintenance des index dans SQL Server. Ce outil permet de choisir automatiquement entre la réorganisation ou la reconstruction des index en fonction du niveau de fragmentation.

2.1 Détails du processus IndexOptimize

Le fichier Solution/Ola_Maintenance_Solution/IndexOptimize.sql comprend les fonctionnalités suivantes :

  • Gestion automatique de la fragmentation :
  • Fragmentation faible (<5 %) : Aucune action
  • Fragmentation modérée (5-30 %) : Réorganisation de l'index
  • Fragmentation élevée (>30 %) : Reconstruction de l'index
  • Configuraton flexible des paramètres :
EXEC OptimiseIndex 
  @BasesDonnees = 'ToutesLesBases',
  @FragmentationFaible = NULL,
  @FragmentationMoyenne = 'REORGANISER,RECONSTRUIRE',
  @FragmentationElevée = 'RECONSTRUIRE_EN_LIGNE,RECONSTRUIRE',
  @NiveauFragmentation1 = 5,
  @NiveauFragmentation2 = 30,
  @TaillePage = 1000

2.2 Analyse de l'utilisation des index

Utilisez le script Scripts/Which_Indexes_are_not_Used.sql pour identifier les index non utilisés ou inefficaces :

SELECT 
  o.name AS NomObjet,
  SCHEMA_NAME(o.schema_id) AS Schema,
  i.name AS NomIndex,
  i.Type_Desc AS Type,
  CASE 
    WHEN (s.user_seeks > 0 OU s.user_scans > 0 OU s.user_lookups > 0) ET s.user_updates > 0 
      THEN 'UTILISÉ ET MIS À JOUR'
    WHEN (s.user_seeks > 0 OU s.user_scans > 0 OU s.user_lookups > 0) ET s.user_updates = 0 
      THEN 'UTILISÉ MAIS PAS MIS À JOUR'
    WHEN s.user_seeks IS NULL ET s.user_scans IS NULL ET s.user_lookups IS NULL ET s.user_updates IS NULL 
      THEN 'Pas UTILISÉ ET PAS MIS À JOUR'
    WHEN (s.user_seeks = 0 ET s.user_scans = 0 ET s.user_lookups = 0) ET s.user_updates > 0 
      THEN 'Pas UTILISÉ MAIS MIS À JOUR'
    ELSE 'Aucun des cas ci-dessus'
  END AS InformationUsage
FROM sys.objects AS o
JOIN sys.indexes AS i ON o.object_id = i.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE o.type = 'U' ET i.type IN (1, 2)
ORDER BY user_seeks, user_scans, user_lookups, user_updates ASC;

3. Gestion visuelle des index : Astuces pratiques avec SSMS

L'interface utilisateur de SQL Server Management Studio (SSMS) propose des fonctionnalités de gestion des index. Associé aux scripts sqlserver-kit, il permet une maintenance efficace.

3.1 Analyse des rapports intégrés SSMS

Utilisez les rapports natifs SSMS pour obtenir des informations sur l'utilisation des index :

Étapes de manipulation :

  1. Cliquez droit sur la base de données dans l'Explorateur d'objets
  2. Accédez à « Rapports » > « Rapports par défaut » > « Statistiques d'utilisation des index »

3.2 Configuration des raccourcis clavier

Définissez des raccourcis clavier pour les scripts de maintenance les plus utilisés :

Recommandations :

  • Ctrl+F1 : Afficher les informations sur l'objet
  • Ctrl+2 : Surveiller les sessions actives
  • Ctrl+3 : Exécuter le script de maintenance personnalisé

3.3 Suivi de la progression de la création des index

Utilisez le script Scripts/Index_Creating_Info.sql pour surveiller la progression de la création des index :

WITH agg AS (
  SELECT 
    SUM(qp.row_count) AS LignesTraitées,
    SUM(qp.estimate_row_count) AS LignesTotales,
    MAX(qp.last_active_time) - MIN(qp.first_active_time) AS DureeMS,
    MAX(IIF(qp.close_time = 0 AND qp.first_row_time > 0, physical_operator_name, N'<Transition>')) AS EtapeCourante
  FROM sys.dm_exec_query_profiles qp
  WHERE qp.physical_operator_name IN (N'Table Scan', N'Clustered Index Scan', N'Index Scan', N'Sort')
  AND qp.session_id IN (SELECT session_id FROM sys.dm_exec_requests WHERE command IN ('CREATE INDEX','ALTER INDEX','ALTER TABLE'))
), comp AS (
  SELECT *,
    (TotalRows - RowsProcessed) AS LignesRestantes,
    (ElapsedMS / 1000.0) AS DureeSeconde
  FROM agg
)
SELECT 
  EtapeCourante,
  TotalRows,
  RowsProcessed,
  LignesRestantes,
  CONVERT(DECIMAL(5, 2), ((RowsProcessed * 1.0) / TotalRows) * 100) AS PourcentageTermine,
  DureeSeconde,
  ((ElapsedSeconds / RowsProcessed) * LignesRestantes) AS EstimeeSecondsRestantes,
  DATEADD(SECOND, ((ElapsedSeconds / RowsProcessed) * LignesRestantes), GETDATE()) AS EstimeeCompletionTime
FROM comp;

4. Stratégies d'optimisation avancée

4.1 Analyse et traitement de la fragmentation

Utilisez la vue « Détails de l'objet Explorateur d'objets » pour trier les index par taille :

Indicateurs clés :

  • Espace index (KB) : Espace occupé par l'index
  • Espace données (KB) : Espace occupé par les données
  • Une proportion anormale indique un besoin d'optimisation

4.2 Configuration des options de requête pour améliorer l'efficacité des index

Les modifications des options de requête peuvent considérablement influencer l'utilisation des index :

Réglages recommandés :

  • Activer « SET ARITHABORT »
  • Désactiver « SET NOCOUNT »
  • Définir un « TIMEOUT_LOCK » approprié (3 000-5 000 ms)

5. Maintenance automatisée des index

5.1 Utilisation d'Agents SQL Server

Associez les scripts sqlserver-kit avec les_agents SQL Server pour une maintenance automatisée :

  1. Créez un travail avec la procédure stockée IndexOptimize
  2. Définissez une fenêtre de maintenance (idéalement en dehors des heures de pointe)
  3. Configurez des notifications en cas de succès ou d'erreur

5.2 Exemple de plan de maintenance

-- Création d'un travail exécutant la maintenance des index chaque dimanche à 2h
USE msdb;
GO
EXEC dbo.sp_add_job
  @job_name = N'Maintenance Semaineelle des Index',
  @enabled = 1,
  @description = N'Maintenance automatisée des index avec les scripts sqlserver-kit';

EXEC dbo.sp_add_jobstep
  @job_name = N'Maintenance Semaineelle des Index',
  @step_name = N'Exécuter OptimiseIndex',
  @subsystem = N'TSQL',
  @command = N'EXEC [dbo].[OptimiseIndex] @BasesDonnees = ''BASES_UTILISATEUR'', @FragmentationMoyenne = ''REORGANISER'', @FragmentationElevée = ''RECONSTRUIRE_EN_LIGNE'', @MettreAJourStatistiques = ''TOUT''',
  @database_name = N'master';

EXEC dbo.sp_add_schedule
  @schedule_name = N'Dimanche 2h',
  @freq_type = 8,
  @freq_interval = 1,
  @active_start_time = 20000;

EXEC dbo.sp_attach_schedule
  @job_name = N'Maintenance Semaineelle des Index',
  @schedule_name = N'Dimanche 2h';

6. Conclusions et bonnes pratiques

  1. Vérification régulière : Effectuez une analyse de fragmentation des index une fois par semaine
  2. Traitement personnalisé : Choisissez entre la réorganisation ou la reconstruction en fonction du niveau de fragmentation
  3. Surveillance de l'utilisation : Supprimez régulièrement les index inutilisés
  4. Maintenance automatisée : Utilisez les agents SQL Server pour une maintenance non supervisée
  5. Test et validation : Comparez les performances avant et après maintenance

Grâce aux outils de sqlserver-kit et aux méthodes décrites dans ce guide, même un débutant peut rapidement maîtriser la gestion professionnelle des index dans SQL Server. Une optimisation efficace des index non seulement améliore les performances des requêtes, mais aussi réduit la consommation des ressources du système, offrant une meilleure stabilité aux applications.

Pour commencer, clonez le dépôt : git clone https://gitcode.com/gh_mirrors/sq/sqlserver-kit et explorez les scripts et les meilleures pratiques pour l'optimisation des index.

【Lien utile】sqlserver-kit : Outils, scripts et bonnes pratiques pour Microsoft SQL Server

Étiquettes: SQL Server index maintenance Optimisation performances

Publié le 14 septembre à 13h45

Étiquettes Populaires

©2026 L'Atelier Monstre