Free Press

L’objectif derrière la création d’index SQL sur les tables de base de données est d’améliorer les performances des requêtes qui lisent les données de la table de base en accélérant les processus de récupération des données. Mais ces tables transactionnelles ne sont pas statiques et évoluent très fréquemment dans le temps. Les changements qui sont effectués sur la table de base de données comprennent l’ajout de nouveaux enregistrements, la modification ou la suppression des enregistrements existants. Comme ces changements doivent être répliqués sur les index de table connexes, l’index de table deviendra fragmenté au fil du temps.

SQL Server Index Fragmentation Overview

Il existe deux principaux types de fragmentation d’index SQL ; la fragmentation interne et la fragmentation externe. La fragmentation interne est causée par l’insertion d’un nouvel enregistrement ou la mise à jour d’un enregistrement existant avec des valeurs qui ne tiennent pas dans l’espace libre actuel de la page de données et résulte avec la division de la page en deux pages pour s’adapter à la nouvelle valeur. Dans ce cas, le moteur du serveur SQL déplacera environ 50 % des données de la page actuelle vers la nouvelle page de données générée par l’opération de division, afin de maintenir l’équilibre de l’arbre d’indexation SQL. La fragmentation interne peut également être causée par les opérations de suppression aléatoires qui génèrent des espaces vides sur les pages de données. Ce type d’opérations laisse les pages de données non remplies et résulte avec une taille d’index SQL Server plus grande et des performances plus lentes en raison de la nécessité de lire plus de pages de données pour récupérer les données demandées.

La fragmentation externe est causée lorsque les pages de données de l’index SQL Server sont dispersées sur le fichier physique de la base de données, en raison d’avoir l’ordre logique de l’index SQL Server ne correspond pas à l’ordre physique sur le lecteur de disque sous-jacent. La fragmentation externe peut être causée par des opérations de suppression aléatoires qui laissent un certain nombre de pages de l’étendue vide alors que l’étendue elle-même est réservée. Ce type de fragmentations entraîne une dégradation des performances, car il faudra plus de temps et consommera plus de ressources pour sauter entre les pages de données éparpillées afin de récupérer les données demandées sur le lecteur de disque sous-jacent.

La raison pour laquelle nous nous soucions beaucoup de la fragmentation de l’index SQL est que l’index que vous avez créé pour améliorer les performances du système, peut dégrader les performances de vos requêtes après avoir été fortement fragmenté. Imaginez la différence de temps et de nombre de lectures de disque entre la lecture d’un nombre spécifique de lignes à partir d’un index SQL sain dans lequel les données sont stockées dans des pages de données contiguës au sein du fichier de base de données, et la lecture du même nombre de lignes à partir d’un index SQL Server hautement fragmenté, dans lequel les données sont dispersées dans des pages de données non contiguës au sein du fichier de base de données.

S’il est constaté que l’index SQL de la table est fragmenté, vous devez résoudre le problème de fragmentation en effectuant une réorganisation de l’index SQL Server ou une reconstruction de l’index, en fonction du pourcentage de fragmentation de l’index SQL. Par exemple, un index SQL Server dont le pourcentage de fragmentation est inférieur à 30 % peut être réorganisé, là où l’index SQL dont le niveau de fragmentation est supérieur à 30 % doit être reconstruit.

• Remarque : Consultez la section Maintenance des index SQL Server pour plus d’informations.

Vous pouvez surmonter de manière proactive les problèmes de fragmentation de l’index SQL Server et de fractionnement des pages en concevant l’index correctement et en définissant les options de création d’index Fill Factor et pad_index avec les valeurs propervalues.

• Remarque : Consultez Opérations sur les index SQL Server pour plus d’informations.

Rapport de fragmentation d’index de serveur SQL

Il n’est pas considéré comme une meilleure pratique de simplement créer un index SQL dans votre table de base de données pour tirer des avantages de l’amélioration de l’opération de récupération de données et laisser cet index de serveur SQL pour toujours sans surveiller continuellement sa fragmentation et le maintenir.

SQL Server nous fournit plusieurs méthodes qui peuvent être utilisées pour vérifier le pourcentage de fragmentation de l’index SQL.

Propriétés de l’index SQL Server

La première méthode est la page Fragmentation de la fenêtre Propriétés de l’index SQL Server. Faites un clic droit sur l’index que vous devez vérifier et choisissez l’option Propriétés. À partir de la page Propriétés de l’index SQL Server, parcourez la page Fragmentation, et vous verrez le pourcentage de fragmentation de l’index et combien les pages de l’index SQL Server sont pleines sous la section Fragmentation, comme indiqué ci-dessous :

En quelques étapes, vous pouvez facilement vérifier le pourcentage de fragmentation de l’index SQL Server de l’index sélectionné à partir de la fenêtre des propriétés de l’index. Mais que faire si vous avez besoin d’avoir une vue d’ensemble du pourcentage de fragmentation pour tous les index de la table ou tous les index des tables de la base de données ? Dans ce cas, vous devez aller le vérifier un par un. Par conséquent, une seule semaine de travail ne suffira pas pour un seul rapport sur la base de données !

sys.dm_db_index_physical_stats

Une autre méthode qui peut être utilisée pour vérifier le pourcentage de fragmentation des index de la base de données consiste à interroger la fonction de gestion dynamique sys.dm_db_index_physical_stats, qui a été introduite pour la première fois dans SQL Server 2005, en remplacement de la commande dépréciée DBCC SHOWCONTIG. Elle fournit des informations sur la taille et le pourcentage de fragmentation des index de la base de données.

Afin d’obtenir des informations significatives sur le pourcentage de fragmentation de tous les index sous la base de données spécifiée à partir de la DMF sys.dm_db_index_physical_stats, vous devez la joindre à la DMV sys.indexes DMV, comme dans le script T-SQL ci-dessous:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

En fonction de vos compétences en développement SQL, vous pouvez effectuer le filtrage dont vous avez besoin pour récupérer la fragmentation des index SQL sur la portée requise, sans option pour obtenir un résumé pour l’ensemble des bases de données de l’instance, en tenant compte du fait que les données affichées sont depuis le dernier redémarrage du service SQL Server. Le résultat dans notre cas sera comme:

Rapport standard de statistiques physiques d’index du serveur SQL

Le serveur SQL nous fournit plusieurs rapports standard qui contiennent des informations statistiques utiles sur la base de données sélectionnée. Le rapport Statistiques physiques de l’index, est l’un des rapports standard de SQL Server qui renvoie des informations statistiques sur les partitions d’index de SQL Server, le pourcentage de fragmentation et le nombre de pages sur chaque partition d’index SQL au niveau de la base de données, depuis le dernier redémarrage du service SQL Server, avec une recommandation sur la façon de résoudre le problème de fragmentation de l’index, s’il existe, en fonction du pourcentage de fragmentation de l’index SQL Server.

Pour afficher le rapport Statistiques physiques des index de SQL Server, faites un clic droit sur votre base de données, choisissez Rapports -> Rapports standard puis sélectionnez le rapport Statistiques physiques des index, comme indiqué ci-dessous :

Et le rapport généré au niveau de la base de données, qui ne peut pas être filtré et nécessite de développer manuellement le résultat de chaque index de table, sera comme indiqué ci-dessous :

ApexSQL Defrag

L’outil ApexSQL Defrag est un outil de défragmentation d’index SQL tiers qui peut être facilement utilisé pour vérifier la fragmentation de l’index de SQL Server et les informations d’utilisation et effectuer ou planifier la correction appropriée pour le problème de fragmentation de l’index.

À partir d’un emplacement central, vous pouvez analyser et examiner les informations de fragmentation et d’utilisation à différents niveaux, y compris les informations de fragmentation et d’utilisation de l’index SQL au niveau de l’index du serveur SQL, au niveau de la table, au niveau de la base de données et enfin au niveau de l’instance globale du serveur SQL. Il nous fournit différents types de filtres qui peuvent être utilisés pour afficher et maintenir uniquement vos index cibles.

ApexSQL Defrag nous permet également de configurer le paramètre du facteur de remplissage pour les index de table, de créer des alertes pour tous les travaux et politiques de défragmentation et d’envoyer une notification par Email pour les travaux réussis ou échoués.

Les informations affichées fourniront une bonne vue sur la taille de l’index SQL et le pourcentage de fragmentation, qui ne sera pas affectée par un redémarrage du service SQL Server ou un redémarrage du serveur. En effet, ces informations seront stockées dans une base de données centrale de dépôt pour l’outil ApexSQL Defrag.

Installation d’ApexSQL Defrag

ApexSQL Defrag peut être facilement téléchargé depuis le centre de téléchargement d’ApexSQL et installé sur votre serveur en suivant l’assistant d’installation simple, comme indiqué ci-dessous :

Tout d’abord, il vous sera demandé de spécifier le compte de service qui sera utilisé comme contexte d’exécution pour l’agent ApexSQL Defrag et transmet les commandes à l’instance de SQL Server, comme suit :

Après avoir spécifié le compte de service ApexSQL Defrag, il vous sera demandé de spécifier où installer l’outil ApexSQL Defrag et s’il faut créer une icône de raccourci pour l’outil sur le bureau, comme indiqué ci-dessous :

En vérifiant que le chemin fourni contient au minimum 127MB d’espace libre, ApexSQL Defrag sera installé avec succès sur votre serveur. Et la notification suivante s’affichera, vous offrant la possibilité de démarrer directement ApexSQL Defrag :

Si vous exécutez ApexSQL Defrag pour la première fois, il vous sera demandé de confirmer la création de la base de données du référentiel central, où seront stockées ces données historiques et de configuration, comme suit :

Ajouter un nouveau serveur

Après avoir créé la base de données de dépôt, ApexSQL Defrag sera lancé sans instance SQL. Pour vérifier le pourcentage de fragmentation de l’index SQL sur une instance SQL Server spécifique, vous devez ajouter cette instance à cet outil. Pour cela, cliquez sur le bouton Ajouter, sous l’onglet Accueil, comme indiqué ci-dessous :

Dans la fenêtre Connexion à SQL Server, il vous sera demandé de fournir le nom de l’instance SQL Server qui sera utilisée et les informations d’identification requises pour se connecter à ce serveur, comme suit :

Analyse des index du serveur SQL

Lorsque le serveur est ajouté, vous pouvez effectuer une nouvelle vérification de toutes les informations de fragmentation des index SQL à partir de l’onglet Fragmentation ci-dessous :

ApexSQL Defrag vous offre la possibilité d’analyser tous les index sur cette instance en fonction de trois modes :

• DÉTAILLÉ : dans lequel toutes les données et les pages de l’index SQL seront lues pendant le processus d’analyse
• ÉCHANTILLONÉ : dans lequel seulement 1% des pages seront lues, en tenant compte du fait que l’index du serveur SQL contient plus de 10 000 pages
• LIMITÉ : dans lequel seules les pages situées au niveau parent du b-tree seront lues

Pour analyser les informations de l’index SQL Server de l’instance SQL Server connectée, cliquez sur le bouton Analyser, sous l’onglet Fragmentation, et choisissez d’effectuer une analyse rapide ou profonde. Nous effectuerons une Analyse en profondeur pour obtenir des informations complètes, comme indiqué ci-dessous :

Continuez à surveiller la barre de progression sur le côté inférieur de l’outil pour vérifier la progression de l’analyse, comme suit :

Et un rapport complet, qui contient des informations complètes sur tous les index SQL créés sous chaque base de données de cette instance SQL Server, sera affiché comme indiqué ci-dessous :

Avec la possibilité de plonger entre les différents niveaux ; instance SQL Server, base de données, table ou index, avec un simple clic sous la même fenêtre centrale, et de filtrer le résultat récupéré en fonction d’un niveau ou d’un seuil de fragmentation spécifique, comme indiqué ci-dessous :

En outre, vous pouvez faire glisser n’importe quelle colonne des colonnes du rapport pour regrouper le rapport global en fonction des valeurs de cette colonne, comme dans l’exemple ci-dessous, dans lequel nous avons regroupé les données du rapport en fonction du nom de la Table:

ApexSQL Defrag Reports

ApexSQL Defrag nous fournit un certain nombre de rapports qui montrent des informations statistiques sur les index de la base de données. Ces rapports peuvent être configurés et vérifiés sous l’onglet Rapports ci-dessous :

Le premier rapport est le rapport Total, qui montre une représentation graphique pour la fragmentation des index SQL et l’utilisation de l’espace disque au niveau sélectionné. Par exemple, si vous cliquez sur le nom de l’instance, le rapport dessinera le graphique du pourcentage de fragmentation et le graphique de l’utilisation de l’espace au niveau de l’instance du serveur SQL, comme ci-dessous:

Si vous cliquez sur n’importe quelle base de données à partir de la fenêtre de l’explorateur de serveur, le rapport changera automatiquement la vue pour montrer le graphique au niveau de la base de données, comme indiqué ci-dessous:

Le deuxième rapport, qui peut également être consulté à différents niveaux, est le rapport Statistiques. Ce rapport montre des informations statistiques sur la taille et la fragmentation totales des index et la distribution des types d’index au niveau sélectionné, comme indiqué ci-dessous :

Le troisième rapport est le rapport Top 10, qui affiche les 10 index clusterisés et non clusterisés ayant la plus grande taille et le pourcentage de fragmentation d’index SQL le plus élevé, au niveau spécifié. Par exemple, le rapport suivant montre les 10 index les plus coûteux, en fonction de la taille et de la fragmentation, au niveau de l’instance du serveur SQL :

En descendant vers une base de données spécifique, le rapport montrera automatiquement les 10 index les plus coûteux, en fonction de la taille et de la fragmentation, au niveau de la base de données sélectionnée, comme indiqué ci-dessous :

En plongeant profondément vers une table spécifique sous cette base de données, le rapport montrera les 10 index les plus chers, en fonction de la taille et de la fragmentation, comme suit :

ApexSQL Defrag Report Export

ApexSQL Defrag nous permet d’exporter les rapports de fragmentation vers différents formats de fichiers, dont PDF, IMG, HTML, CS et XML, avec la possibilité de personnaliser le paramètre du rapport avant qu’il ne soit généré. Sous l’onglet Rapports, cliquez sur le bouton Options:

Depuis la fenêtre Modifier les options d’exportation, vous pouvez configurer les différentes options, en commençant par la convention de dénomination du fichier généré, le chemin où le fichier sera enregistré, l’orientation de la page et enfin le contenu de chaque rapport, comme indiqué ci-dessous :

Par exemple, pour exporter le rapport Total généré au niveau spécifié, cliquez sur le bouton Exporter, sous l’onglet Rapports, et choisissez le type de fichier, vers lequel ce rapport Total sera exporté. Dans quelques secondes, le rapport sera généré, vous demandant si vous voulez ouvrir le rapport généré. Cliquez sur Oui pour ouvrir ce rapport directement, comme indiqué ci-dessous:

En outre, vous pouvez exporter le rapport Top 10 au niveau spécifié, en cliquant sur le bouton Exporter, sous l’onglet Rapports, et spécifier le type de fichier d’exportation, et le rapport sera généré rapidement, en vous demandant si vous voulez voir le rapport généré directement. Cliquez sur Oui pour ouvrir le généré, comme indiqué ci-dessous:

Il est clair à partir des exemples précédents, que nous pouvons facilement utiliser l’outil ApexSQL Defrag pour examiner et analyser les informations du pourcentage de fragmentation de l’index SQL et plonger au niveau différent en commençant par le niveau de l’instance du serveur SQL jusqu’au niveau de l’index du serveur SQL et l’afficher dans différents formats exportables, sans que les informations affichées soient affectées par un quelconque redémarrage du serveur ou du service, car elles seront stockées dans une base de données de dépôt.