Par: Brady Upton | Mise à jour: 2016-06-22 | Commentaires (5) | Connexes:Plus >Fragmentation et maintenance de l’index
Problème
Nous avons tous entendu parler de la fragmentation de la base de données / index (et si ce n’est pas le cas, continuez à lire), mais qu’est-ce que c’est? Est-ce un problème? Comment savoir s’il se trouve dans ma base de données ?Comment le réparer ? Ces questions pourraient être une astuce en soi, mais je vais essayer de vous donner une idée de chacune dans cette astuce.
Solution
Sans entrer dans beaucoup de détails, SQL Server stocke les données sur des pages de 8 Ko. Lorsque nous insérons des données dans une table, SQL Server allouera une page pour stocker que les données insérées ne dépassent pas 8 Ko dans lesquelles elles s’étaleraient sur plusieurs pages.Chaque page est affectée à un tableau. Si nous créons 10 tables, nous aurons 10 pages différentes.
Lorsque vous insérez des données dans une table, les données vont d’abord dans le fichier journal des transactions. Le fichier journal de transaction est un enregistrement séquentiel, ce qui signifie que lorsque vous insérez, mettez à jour et supprimez des enregistrements, le journal enregistrera ces transactions du début à la fin. Le fichier de données en revanche n’est pas séquentiel. Le fichier journal videra les données vers le fichier de données créant des pages partout.
Maintenant que nous avons une idée de la façon dont les données sont stockées, qu’est-ce que cela a à voir avec la fragmentation?
Il existe deux types de fragmentation: la fragmentation interne et la fragmentation externe.
Fragmentation interne de SQL Server
La fragmentation interne de SQL Server est causée par des pages qui ont trop d’espace libre.Supposons qu’au début de la journée, nous ayons un tableau avec 40 pages qui sont pleines à 100%, mais à la fin de la journée, nous avons un tableau avec 50 pages qui ne sont que pleines à 80% en raison de diverses instructions de suppression et d’insertion tout au long de la journée.Cela pose un problème car maintenant, lorsque nous devons lire dans ce tableau, nous avons 50 pages au lieu de 40, ce qui devrait entraîner une diminution des performances.Voyons un exemple rapide et sale.
Disons que j’ai la table suivante avec une clé primaire et un index non clusterisé sur FirstNameand LastName:
Je parlerai des moyens d’analyser la fragmentation plus tard dans cette astuce, mais pour l’instant, nous pouvons cliquer avec le bouton droit de la souris sur l’index, cliquer sur Propriétés et Fragmentation pour voir fragmentationet la plénitude de la page. C’est un tout nouvel indice, donc il est à 0% de fragmentation.
INSÉRONS 1000 lignes dans ce tableau:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
Maintenant, vérifions à nouveau notre index:
Vous pouvez voir que notre index devient fragmenté à 75% et que le pourcentage moyen de pages complètes (plénitude de page) augmente à 80%. Cette table est encore si petite que la fragmentation de 75% ne causerait probablement aucun problème de performances, mais à mesure que la taille de la table augmente et que le nombre de pages augmente, vous pouvez voir les performances se dégrader. Vous pouvez également voir sur la photo ci-dessus que ce tableau est passé de 0 pages à 4.
Fragmentation externe de SQL Server
La fragmentation externe est causée par des pages en panne. Au début de la journée, nous avons une table parfaitement ordonnée. Au cours de la journée, nous émettons des centaines d’instructions de mise à jour, laissant éventuellement un espace vide sur une page et essayant d’adapter l’espace à d’autres pages. Cela signifie que notre stockage doit se déplacer pour obtenir les données nécessaires au lieu de les lire dans une direction.
Analyse de la fragmentation de SQL Server
La fragmentation est-elle donc un problème ? Je crois que oui. Si vous pouvez stocker toute votre base de données en mémoire ou si votre base de données est lue uniquement, je ne m’en inquiéterais pas, mais la plupart d’entre nous n’ont pas ce luxe.J’ai travaillé sur des milliers de serveurs et l’analyse des niveaux de fragmentation est l’une des premières choses que je regarde. En fait, juste en corrigeant la fragmentation, j’ai vu jusqu’à200% d’améliorations des performances des requêtes.
En parlant d’analyser les niveaux de fragmentation, vous vous demandez peut-être comment nous pouvons le faire. Eh bien, il y a plusieurs façons
DBCC SHOWCONTIG – cette fonctionnalité est ancienne et sera supprimée dans les versions futures de SQL Server, mais si vous utilisez toujours SQL Server 2000 ou ci-dessous, cela vous aidera. Au lieu d’écrire à ce sujet, je vous indiquerai ici ou vous pouvez consulter le conseil de Chad Boyd ici. Les deux sont de bonnes ressources.
sys.dm_db_index_physical_stats – Introduite dans SQL Server 2005, cette vue de gestion dynamique (DMV) renvoie des informations de taille et de fragmentation pour les données et les index de la table ou de la vue spécifiée.
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)ORDER BY avg_fragmentation_in_percent DESC
C’est probablement la méthode la plus utilisée pour analyser la fragmentation. Vous pouvez voir sur la capture d’écran ci-dessus que j’ai un index nommé IX_RPCust_1 sur la table de CUST qui est fragmenté à 98,83%. Vous pouvez voir plus d’informations sur ce dmvhéréde Arshad Ali.
Logiciel tiers – Un logiciel tiers aidera à identifier les fragmentationsde sorte que vous n’avez pas à le faire. Greg Robidoux offre un bon conseil sur le gestionnaire de défragmentation SQL d’Idera ici.
Correction de la fragmentation de SQL Server
Maintenant que nous avons trouvé la fragmentation dans la base de données, comment la réparer? Comme les index d’analyse, il existe plusieurs façons.
Plans de maintenance SQL Server – Les plans de maintenance sont livrés avec SQLServer et conviennent à certaines tâches. La maintenance de l’index à mon avis n’en est pas une. Vous pouvez ajouter une tâche d’index de reconstruction ou réorganiser une tâche d’index dans le plan de maintenance, mais le problème est que vous ne pouvez pas vraiment appliquer de logique au plan. Sans Logic, le plan reconstruira / réorganisera CHAQUE index. Certains index peuvent ne pas être fragmentés, ils n’ont donc pas besoin d’être reconstruits. Certains index peuvent avoir une fragmentation minimale, de sorte qu’ils seraient bien avec une réorganisation. Il n’y a aucun moyen de le spécifier dans un plan de maintenance. InSQL Server 2016 il existe de nouvelles options pour gérer la fragmentation.
Script personnalisé – Les scripts personnalisés sont probablement la voie à suivre car vous pouvez appliquer une logique pour vérifier la fragmentation de l’index, puis décider de sauter l’index, de réorganiser ou de reconstruire. J’utilise les scénarios d’Ola Hallengren et ils font exactement ce que j’ai besoin d’eux pour faire. Conseils du MSSQ.com a également cesressources – Conseils de fragmentation SQL Server et de maintenance des index.
Logiciel tiers – Un logiciel tiers aidera à défragmenter l’index pour que vous n’ayez pas à le faire.
Prochaines étapes
- Cette astuce vous donne une idée de quelques-uns des composants impliqués dans la fragmentation de l’index. MSSQLTips.com offre des conseils plus complets axés sur chacun des sujets que j’ai mentionnés ici.
- Assurez-vous de consulter le didacticiel sur la fragmentation de SQL Server de Chad Boyd qui offre une grande perspicacité sur de nombreux sujets concernant la fragmentation
Dernière mise à jour: 2016-06-22
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.View all my tips
- More SQL Server DBA Tips…