Pourquoi, quand et comment reconstruire et réorganiser des indexes dans SQL Server

Le but d’un index dans SQL Server est fortement similaire à l’index d’un livre en ce sens qu’il permet à l’utilisateur de retrouver une information plus rapidement que s’il fallait parcourir le livre dans son intégralité.

Les index de SQL Server sont créés au niveau des colonnes d’une table ou d’une vue. Un index contient des données permettant d’accélérer la localisation des valeurs des colonnes sur lesquelles il est construit. Lorsqu’un index est créé sur la clé primaire d’une table T, celui-ci sera utilisé par SQL Server dès qu’aura lieu une recherche d’une ligne de la table T basée sur cette clé primaire. SQL Server récupèrera dans l’index l’information lui permettant de localiser la ligne correspondante de la table T. Cela signifie que SQL Server n’est pas obligé d’effectuer un parcours complet de la table (full table scan) pour retrouver une ligne de données, ce qui est engendre un gain de performance substantiel car l’opération est plus rapide et moins consommatrice en ressources. Lego Chargeuse Pelleteusepépine10666 Pelleteusepépine10666 Lego Chargeuse Lego Chargeuse Pelleteusepépine10666 X0PNwOknZ8

Les index relationnels peuvent être créés même avant que des données ne soient insérées dans la table sur laquelle ils sont basés. Il est également possible de créer des indexes sur des tables et des vues d’autres bases de données.

Toys Et Pour Photos CamérasMaxi Appareils Enfant MzpqUSV
CREATE INDEX MyIndex ON MyTable (Column1);

Vous trouverez plus d’information sur la commande Transact-SQL CREATE INDEX sur MSDN.

Après qu’un index ait été créé pour une table T, il y aura de façon transparente des maintenances automatiques effectuées par le moteur de base de données de SQL Server dès qu’une opération INSERT, UPDATE, DELETE a lieu sur les données de la table T.

Cependant, ces maintenances automatiques vont continuellement disperser les informations contenues par l’index au sein de la base de données, fragmentant l’index au cours du temps. Résultat des courses: les index ont maintenant des pages dont l’ordre logique (basé sur la valeur de clé) diffère de l’ordre physique au sein du fichier de données. Cela signifie qu’il y a un grand pourcentage d’espace libre dans les pages d’index et que SQL Server doit lire un nombre de pages plus important lorsqu’il scanne chaque index. De plus, l’ordre des pages qui appartiennent au même index se retrouvent mélangées et cela requiert un travail supplémentaire de la part de SQL Server lors d’une lecture d’un index et particulièrement en termes d’entrées/sorties.

Jeux Eveil Pour Oxybul Jouets Rangement HYDIWE92

L’impact de la fragmentation des index sur SQL Server peut aller d’une simple baisse de l’efficacité des requêtes (pour les serveurs avec un impact faible sur les performances) jusqu’à la décision par SQL Server de ne plus utiliser les index et se remettre à faire des lectures complètes des tables (full table scans) quelle que soit la requête. Comme mentionné précédemment, les full table scans vont avoir un impact direct et important sur les performances de SQL Server. C’est la dernière alarme vous indiquant qu’il y a une fragmentation sur les index d’une base de données SQL Server.

La solution à la fragmentation des index est de les réorganiser ou de les reconstruire.

Mais, avant de considérer la maintenance des index il est important de répondre à deux questions:

1. Quel est le degré de fragmentation?

2. Quelle est l’action la plus appropriée? Réorganiser ou reconstruire?

Détecter la fragmentation

Généralement, pour résoudre un problème, il est essentiel de d’abord le localiser et d’isoler la partie affectée avant d’appliquer la solution adéquate.

La fragmentation peut être détectée facilement en appelant la fonction système sys.dm_db_index_physical_stats, qui retourne des informations sur la taille et la fragmentation des index sur des tables ou des vues de SQL Server. Cette fonction peut être exécutée pour récupérer les informations d’un index en particulier, tous les index d’une table donnée ou tous les index de la base de donnée:

Les résultats retournés après exécution de la procédure incluent les informations suivantes:

  • avg_fragmentation_in_percent – pourcentage moyen de pages incorrectes dans l’index
  • fragment_count – nombre de fragments dans l’index
  • avg_fragment_size_in_pages – nombre moyen de pages dans un fragment d’indexJeux Eveil Pour Oxybul Jouets Rangement HYDIWE92

Analyser les résultats d’une détection

Après que la fragmentation ait été détectée, il faut déterminer l’impact de cette fragmentation sur SQL Server puis l’action à effectuer pour chaque cas.

Il n’y a aucune information exacte sur la quantité minimum de fragmentation à partir de laquelle elle peut engendrer des problèmes spécifiques dans SQL Server et causer des problèmes de performance ou de congestion, d’autant plus que les environnements SQL Server varient d’un système à un autre.

Voitures D'urgence Jeu Véhicules En Bus D'éveil Jouets Bois Nimnik DH2WEIYe9

Cependant, il existe une solution généralement acceptée basée sur le pourcentage de fragmentation (la colonne avg_fragmentation_in_percent retourné par la fonction système sys.dm_db_index_physical_stats décrite plus haut):

  • Fragmentation sous 10% – pas de défragmentation requise. Il est généralement accepté dans la majorité des environnements qu’une fragmentation sous les 10% est négligeable et que son impact sur les performances est négligeable
  • Fragmentation entre 10% et 30% – il est suggéré d’effectuer une réorganisation de l’index
  • Fragmentation au dessus des 30% – il est suggéré de reconstruire l’index

Voici le raisonnement qui a poussé à cette solution basée sur des seuils qui pourrait vous aider à déterminer si l’on doit effectuer une reconstruction ou une réorganisation:

La réorganisation des index est un processus où SQL Server parcourt un index existant et le remet en ordre. La reconstruction d’un index est un processus lourd où l’index est supprimé et recréé de toute pièce dans une nouvelle structure, libérée de toute fragmentation ou de tout espace entre ses pages.

Alors que la réorganisation est un processus de nettoyage de structure qui laisse le système en l’état sans verrouiller les tables ou les vues affectées, le processus reconstruction verrouille la table ou la vue sur laquelle l’index considéré est construit pour toute la durée de la reconstruction. Cela signifie que de longues périodes d’arrêt peuvent survenir et ce n’est pas toujours une situation acceptable dans certains environnements.

Si nous gardons cela à l’esprit, nous pouvons clairement considérer le processus de reconstruction d’index comme la “manière forte” pour résoudre le problème, en gardant à l’esprit qu’elle a un coût à savoir le verrouillage plus ou moins long de l’objet sur lequel l’index est basé. Sur Creator Voiture Ou Cher Lego Pas D'occasion Rakuten dhQrCtxs

D’un autre côté, la réorganisation d’index est une solution à moindre coût qui résoudra le problème de la fragmentation de manière moins efficace vu que l’index nettoyé sera toujours moins performant qu’un index reconstruit de toute pièce. Mais la réorganisation est bien meilleure du point de vue efficacité puisqu’il ne verrouille pas l’objet sous-jacent pendant l’opération.

Les serveurs avec des périodes de maintenance régulières (par exemple, chaque week-end) devraient théoriquement toujours opter pour une reconstruction d’index, peu importe le pourcentage de fragmentation vu que ces environnements seront à peine affectés par des verrouillages sur les objets associés imposés par les reconstructions d’index.

Comment réorganiser et reconstruire un index:

Avec SQL Server Management Studio (SSMS):

  1. Dans l’explorateur d’objets, naviguer jusqu’à la base de données de votre choix en étendant le noeud Database (Base de données) sous le noeud correspondant à votre instance SQL Server Cadeau Enfants Rc Peut Dérive Jouet 163 Cool Voiture Road Vente Pour Mini Haute Ring Light Pull Off Chaude En Plastique De 7fvYb6gy
  2. Etendre le noeud correspondant à la base de données présentant une fragmentation sur un de ses index
  3. Aller dans le noeud Tables et aller sur la table avec un index fragmentée
  4. Développer le noeud de cette table
  5. Développer le noeud Indexes
  6. Cliquer droit sur l’index fragmenté et sélectionner l’option Rebuild (Reconstruire) ou Reorganize (Réorganiser) dans le menu contextuel (selon l’action que vous voulez faire)

  7. Lepin Automatique En Plastique Fille Nouveau Orthographe Blocs Enfants Voiture Station De Fang Alpinia Lavage Insérer n80OwPk
  8. Cliquer sur le bouton OK et attendez que le processus se termine

Réorganiser les index d’une table en Transact SQL

Munissez vous du nom de la base de données et de la table où un index fragmenté a été détecté, ajustez puis exécutez le code suivant dans SSMS pour réorganiser tous les index de la table:

USE MyDatabase;
GO

ALTER INDEXJeux Eveil Pour Oxybul Jouets Rangement HYDIWE92 ALL ON MyTable REORGANIZE1 20 Rc 4wd 2ch Oui Kmh Canaux 25 Électrique 2 2 4g Escolher Voiture shrQBotdCx;
GO

Reconstruction des index d’une table en Transact SQL

Munissez vous du nom de la base de données et de la table où un index fragmenté a été détecté, ajustez puis exécutez le code suivant dans SSMS pour reconstruire tous les index de la table:

USE MyDatabase;
GO

ALTERAutomobiles Petites Guinée De Annonces Premier Site y08mOnvwNP INDEX ALL ON MyTable REBUILD;
GO

Réorganiser les index avec ApexSQL Backup

La dernière option pour réorganiser ou reconstruire les index consiste à utiliser ApexSQL Backup – un outil pour la gestion et l’automatisation de tâches liées aux bases de données.

ApexSQL Backup permet à ses utilisateurs d’effectuer et de planifier des jobs de défragmentation depuis une interface conviviale et facile d’utilisation. En plus des fonctionnalités d’automatisation, qui permettent à ses utilisateur d’effectuer des maintenances régulières de leurs index, ApexSQL Backup permet aussi de reconstruire/réorganiser les index de plusieurs tables/vues en même temps. Mais encore, toutes les tâches planifiées, ainsi que toutes les activités terminées, peuvent être inspectées et modifiées au travers d’écrans faciles à utiliser.

Pour planifier une tâche de réorganisation/reconstruction d’index avec ApexSQL Backup, il suffit de suivre les étapes suivantes:

Renault Anciens Renault JouetsJeux JouetsJeux Norev Anciens Norev rBQodCWeEx
  1. Démarrer l’application
  2. Dans le ruban principal, choisissez la tâche de défragmentation appropriée:

  3. Fournir au programme les détails concernant le serveur et la base de données, choisir les tables et les vues à inclure dans la tâche de défragmentation et cliquer sur OK:

  4. Pour remplir les détails de planification, cliquer sur le bouton radio “Schedules” (Planification) et choisir la fréquence et la durée appropriée. Ensuite, cliquer sur le bonton Create (Créer):

  5. Pour terminer la planification, cliquer sur le bouton Create (Créer):

Après cela, la tâche de réorganisation/reconstruction a été créée et sera exécutée conformément aux valeurs entrées pour la planification. Toutes les tâches planifiées peuvent être vues dans le panneau Schedules (Planification).

 

September 1, 2017