L’une des variables les plus critiques pour les performances des bases de données est la fragmentation des index SQL. Nous pouvons être confrontés à des blocages, des impasses, des problèmes d’E/S, des problèmes de déversement sur le disque si le niveau de fragmentation n’est pas géré de manière appropriée. Cela peut également conduire à un plan d’exécution inapproprié aussi.
DMVs
Dans SQL Server, nous pouvons utiliser la DMV sys.dm_db_index_physical_stats pour obtenir les informations sur le niveau de fragmentation de l’index, le nombre de pages et les types d’index en utilisant ceci.
SSMS
Nous pouvons également utiliser les rapports intégrés de SQL Server ‘Index Physical Statistics’ dans SSMS
Nous obtenons les statistiques physiques d’index pour la base de données dans le format suivant.
Nous sommes limités à l’exécution de ce rapport au niveau de la base de données, et si nous avons plusieurs bases de données dans l’instance et de nombreuses instances à gérer, cela devient une tâche consommatrice de temps et de ressources pour collecter les détails, analyser le statut en fonction de notre statut de seuil et ensuite décider de la reconstruction ou de la réorganisation de l’index SQL.
Scripts personnalisés
Nous pouvons également utiliser des scripts personnalisés pour détecter et réparer les problèmes de fragmentation, mais c’est également un processus compliqué pour établir et analyser les résultats manuellement. Nous pourrions vouloir toutes ces informations dans un lieu centralisé et décider ensuite de la politique de maintenance de l’index. Nous pouvons utiliser les commandes Alter Index pour supprimer le niveau de fragmentation. Pour les petites bases de données, nous ne nous soucions pas beaucoup du nombre de pages lors de la reconstruction de l’index, cependant pour les grandes bases de données, nous devons également les prendre en compte.
Outils tiers
Nous pouvons facilement surveiller, analyser et supprimer la fragmentation de l’index SQL existant en utilisant ApexSQL Defrag. Nous pouvons gérer plusieurs instances et bases de données dans un outil graphique. Nous obtenons les avantages suivants de cet outil.
- Il fournit une interface utilisateur graphique pour surveiller et analyser la fragmentation de l’index
- Nous pouvons faire la maintenance de l’index pour toutes les instances et bases de données de SQL Server
- Il fournit le moyen de configurer les rapports personnalisés basés sur notre exigence
- Nous pouvons configurer des politiques pour vérifier et effectuer des tâches de maintenance de l’index selon les métriques personnalisées
- Nous pouvons analyser la fragmentation de l’index (rapide ou profonde), définir le facteur de remplissage, la maintenance de l’index en ligne
- Nous pouvons préparer les rapports dans les formats souhaités (CSV ou HTML)
Une fois que vous avez installé l’outil ApexSQL Defrag et configuré une instance à surveiller, il montre rapidement les détails de l’état de fragmentation de l’index SQL.
Nous devrions éviter les opérations de reconstruction ou de réorganisation des index SQL si elles ne sont pas nécessaires. Si nous avons de très grands index, alors cela pourrait prendre beaucoup de temps et plus de ressources système à analyser. ApexSQL Defrag donne l’option de vérifier la fragmentation.
- Limitée : Dans ce mode d’analyse, SQL Server collecte des informations limitées sur la fragmentation. Il ne cause aucun problème de performance au système. C’est le mode par défaut dans ApexSQL Defrag
- Échantillonné : Dans ce mode, il prend une portion d’échantillon de l’index SQL et recueille les informations de fragmentation
- Détaillé : Nous obtenons la connaissance détaillée de toutes les pages de l’index ; cela pourrait entraîner une surcharge des ressources du système cependant, nous pouvons l’utiliser si nous observons la fragmentation élevée avec les modes de balayage ci-dessus
De même, nous pouvons définir le niveau de fragmentation dans la catégorie élevée, moyenne et faible.
Vous pouvez également définir votre seuil de fragmentation gagné pour analyser les index.
Une fois que nous avons analysé l’ensemble des résultats, faites un clic droit sur l’index SQL particulier et vous pouvez avoir plusieurs options à choisir.
- Analyser (rapide)
- Analyser (profond)
- Réorganiser\Rebuild\Rebuild online
- Définir le facteur de remplissage
- Nous pouvons mettre en pause, arrêter ou annuler si un travail est en cours d’exécution pour cet index. C’est utile si nous voyons des problèmes de performance ou de blocage dus à un index particulier, nous pouvons annuler ce travail lui-même
ApexSQL Defrag permet de créer les politiques à partir des modèles prédéfinis ou par les paramètres personnalisés. Le modèle est également adapté à la plupart des exigences de défragmentation d’index SQL.
En utilisant ces modèles, nous pouvons décider de défragmenter tous les index ou l’index basé sur le niveau de seuil en mode hors ligne ou en ligne. Par exemple, je veux créer la politique pour Reconstruire en ligne le top 50% des index les plus fragmentés de plus de 30% ou réorganiser si fragmenté de plus de 10%.
Nous pouvons configurer le seuil des ressources concernant le CPU, la charge de la mémoire, l’utilisation du journal des transactions, les transactions actives ainsi. Nous n’obtenons pas ce niveau de contrôle sur la maintenance des index en utilisant les jobs de l’assistant de maintenance ou les scripts personnalisés.
Si la ressource est occupée, nous pouvons également définir le timing de réessai. J’ai vu les exigences chez certains utilisateurs qui ne veulent pas exécuter la maintenance après la fin de leur période d’utilisation allégée de la base de données. Si nous effectuons la maintenance, nous devons arrêter le travail manuellement, ou nous devons créer un autre travail d’agent SQL pour arrêter le travail de maintenance de l’index à un moment particulier.
ApexSQL Defrag fournit un surcoût pour configurer et surveiller plusieurs travaux. Nous pouvons personnaliser pour annuler le travail de maintenance de l’index après ‘N’ nombre d’heures.
Dans la capture d’écran ci-dessus, vous pouvez voir que nous pouvons personnaliser davantage l’opération de maintenance de l’index SQL par le niveau de fragmentation, la taille de l’index et le nombre de pages également. Vous pouvez définir le délai de l’objet verrouillé par un temps particulier. Parfois, nous voulons exclure l’index où nous avons des comptes de pages d’index plus petits ou plus grands que les comptes spécifiés.
Vous pouvez effectuer les changements et les configurer comme un modèle à réutiliser. Une fois que nous avons effectué la maintenance de l’index, dans l’onglet Activités, vous obtenez un excellent aperçu des statistiques de fragmentation de l’index avant et après la maintenance. Il est essentiel de savoir si le niveau de fragmentation de l’index est sous contrôle ou non après la maintenance. Nous devons généralement capturer le niveau de fragmentation à nouveau, mais cela nous épargne les frais généraux avec cette information.
Dans la section Rapports, nous pouvons voir les rapports du niveau du serveur, rapport global.
Nous pouvons également obtenir une belle vue des 10 principaux index clusterisés et non clusterisés ainsi.
Nous sommes également intéressés à connaître le comportement du CPU et de la mémoire pendant que la maintenance de l’index est en cours d’exécution. Nous pouvons obtenir cette information utile et garder un œil sur les ressources du système également.
Nous pouvons exporter tous les rapports et configurer l’option d’exportation. Nous pourrions vouloir que des informations limitées soient extraites en l’explorant. ApexSQL Defrag vous donne la possibilité de choisir les informations à afficher telles que les graphiques, les statistiques, les graphiques de statistiques d’index SQL que nous voulons inclure dans le rapport.
Conclusion:
L’outil ApexSQL Defrag facilite la tâche administrative des DBA avec une meilleure surveillance et maintenance des index SQL. Vous obtenez toutes les informations est un outil graphique avec des visuels personnalisés, les politiques ainsi que divers rapports informatifs.
