Monitorer la fragmentation SQL Server 7 : dbcc showcontig

Introduction

DBCC SHOWCONTIG est une commande qui apporte une aide précieuse pour comprendre des éventuels problèmes de performances liés à la fragmentation des données dans une table. Il peut également s’agir de fragmentation forte dans un index.

Stockage des données sous MS SQL Server

Pour mieux comprendre les points évoqués par la suite, ci-dessous est donnée une représentation graphique sur la manière dont SQL Server arrange les données :

La taille d’une ligne est généralement indiquée dans la définition de la table. Pour SQL Server 7, une table peut définir une ligne dont la taille varie entre 4 bytes et 8060 bytes. Cette limite est conditionnée par la taille d’une page de données qui peut stocker jusqu’à 8192 bytes (8Kb), les 512 bytes restants sont utilisés par SQL Server pour stocker les informations de chaînage de pages, etc. (offsets).

SQL Server fonctionne avec des pages de 8Kb, toutefois l’unité minimale de données allouée par le moteur SQL Server est fixée à 64Kb, ce qui correspond à un extent.

Contexte de fragmentation sous MS SQL Server

Pour stocker les données dans un ordre précis, SQL Server utilise pour cela les indexes clusterisés. Lorsqu’une base de données est créée, les indexes clusterisés existent pratiquement pour toutes les tables. Si les données sont effectivement triées en adéquation avec l’index clusterisé sur une page de données, il n’en est pas forcément de même au niveau des extents.

La raison à ce phénomène provient de situations pour lesquelles il n’existe plus de place disponible sur une page de données pour l’insertion d’une ligne qui respecte l’index clusterisé. Dans ce contexte, SQL Server déplace approximativement une moitié de la page vers une autre page appelée Page Split (ce phénomène ne se produit pas pour les indexes clusterisés basés sur des colonnes de type identity). Ce déplacement de page peut se produire sur un autre extent éventuellement spécialement alloué.

A titre d’exemple, supposons un index clusterisé basé sur le nom dans une table. La première page contient initialement les noms allant de A à H sur une page, sur la page suivante les noms vont de I à Z. Avec le temps, il est possible d’avoir une page dans le premier extent qui contient les noms allant de A à C, une page dans un autre extent qui contient les noms allant de D à E et la cinquième page du premier extent qui contient les noms allant de S à Z, etc. C’est typiquement le phénomène de page split sur un index clusterisé. Dans ce contexte, les conséquences sur les performances sont dramatiques et la commande DBCC SHOWCONTIG permet de traquer rapidement ce type de problème.

Syntaxe

DBCC SHOWCONTIG [ ( id_table [, id_index] )]

Arguments

Analyses effectuées par la commande DBCC SHOWCONTIG

Cette instruction parcourt la chaîne de la page au niveau feuille de l’index indiqué lorsque id_index est précisé. Si id_table est précisé ou si la valeur de id_index correspond à 0, les pages de données de la table indiquée sont balayées.

DBCC SHOWCONTIG détermine si la table est fragmentée de manière importante ou non. La fragmentation de la table a lieu lors du processus de modification de données (instructions INSERT, UPDATE et DELETE) effectuées sur la table. Comme ces modifications ne sont pas généralement distribuées de manière égale parmi les lignes de la table, le « remplissage » de chaque page peut varier selon le moment. Pour les requêtes qui balaient une partie ou la totalité d’une table, ceci peut provoquer des lectures de page supplémentaires.

Lorsqu’une table est fragmentée de manière importante, il est nécessaire de réduire et améliorer les performances de la lecture anticipée (analyse de données parallèle) en supprimant et en recréant un index organisé en clusters (sans utiliser l’option SORTED_DATA). La nouvelle création d’un index organisé en clusters permet de réorganiser les données, ce qui a pour résultat des pages de données remplies entièrement. Le niveau de « remplissage » peut être configuré à l’aide de l’option FILLFACTOR.

Exemple d’appel de la commande DBCC SHOWCONTIG

Il est possible très simplement d’extraire dynamiquement un top 10 des tables les plus fragmentées avec un script SQL :

SELECT TOP 10
  'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')'
  + CHAR(10) +
  'PRINT '' ''' + CHAR(10)
FROM sysindexes
WHERE indid = 1 or indid = 0
ORDER BY rows DESC 

Interprétation de résultats de la commande DBCC SHOWCONTIG

Voici deux exemples de résultats de la commande DBCC SHOWCONTIG :

DBCC SHOWCONTIG scanning 'Table1' table...
Table: 'Table1' (1556968673); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 18986
- Extents Scanned..............................: 2443
- Extent Switches..............................: 9238
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 25.70% [2374:9239]
- Logical Scan Fragmentation ..................: 44.58%
- Extent Scan Fragmentation ...................: 87.07%
- Avg. Bytes Free per Page.....................: 1658.7
- Avg. Page Density (full).....................: 79.51%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

DBCC SHOWCONTIG scanning 'Table2' table...
Table: 'Table2' (183984032); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 28980
- Extents Scanned..............................: 3687
- Extent Switches..............................: 22565
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 16.06% [3623:22566]
- Logical Scan Fragmentation ..................: 83.05%
- Extent Scan Fragmentation ...................: 87.44%
- Avg. Bytes Free per Page.....................: 3151.1
- Avg. Page Density (full).....................: 61.07%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

Pour la première table Table1, 18 986 pages ont été examinées pour la création du rapport. Ces pages sont implémentées dans 2 443 extents, indiquant ainsi que la table consomme approximativement 97% des extents qui lui sont alloués (7.8 pages par extent en moyenne).

Le paramètre « Extent switches » indique que lors de l’examen de la table Table1, le serveur a été contraint de switcher entre les extents 9 238 fois (phénomène de split).

La paramètre « Scan Density » confirme ce qui a été vu précédemment, en effet ce dernier paramètre donne le pourcentage de toutes les pages dans les extents qui sont contigües, la densité doit être la plus proche possible de 100.

Les paramètres « logical scan fragmentation » et « extent scan fragmentation » donnent des indications sur l’état de fragmentation des indexes clusterisés. Dans les deux cas, ces deux paramètres doivent être le plus proche possible de 0, lorsque ces paramètres tendent vers 100, le phénomène de page split sur l’index clusterisé s’accroît.

Les autres informations pour la table Table1 sont le nombre moyen de bytes libre par page et la densité moyenne pour les pages (remplissage des pages).

Lorsque le remplissage des pages tend vers 100, cela optimise la lecture par l’optimiseur de SQL Server mais bien sur l’insertion de données engendrant un split de pages est pénalisante. Un bon compromis consiste à indiquer un pourcentage maximal de remplissage pour chaque page (paramètre FILLFACTO dans la commande CREATE TABLE).

La commande DBCC DBREINDEX est l’outil qui permet de réorganiser une table ou un index. Après lancement de la commande DBCC DBREINDEX, les nouveaux jeux de résultats sont les suivants pour les tables Table1 et Table2.

DBCC SHOWCONTIG scanning 'Table1' table...
Table: 'MyTable1' (1556968673); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 15492
- Extents Scanned..............................: 1945
- Extent Switches..............................: 2363
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 81.94% [1937:2364]
- Logical Scan Fragmentation ..................: 15.43%
- Extent Scan Fragmentation ...................: 20.15%
- Avg. Bytes Free per Page.....................: 159.8
- Avg. Page Density (full).....................: 98.03%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

DBCC SHOWCONTIG scanning 'Table2' table...
Table: 'MyTable2' (183984032); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 35270
- Extents Scanned..............................: 4415
- Extent Switches..............................: 4437
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.35% [4409:4438]
- Logical Scan Fragmentation ..................: 0.11%
- Extent Scan Fragmentation ...................: 0.66%
- Avg. Bytes Free per Page.....................: 3940.1
- Avg. Page Density (full).....................: 51.32%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

Pour la table Table1, il n’existe plus que 2363 switch d’extents, cette diminution est grandement liée à une meilleure organisation des données, le pourcentage de données contigues étant monté à près de 80%.

La table Table2 ne présente pas en revanche d’améliorations dans les statistiques rapportées par la commande DBCC SHOWCONTIG concernant le paramètre « Avg. Page Density ». L’explication en est simple : pour cette table l’option FILLFACTOR a été fixé à 0.5 (soit 50% de remplissage d’une page) pour permettre des insertions concurrentes sans générer des splits de page très pénalisants. L’espace alloué est donc plus important, d’où l’augmentation du nombre d’extents scannés après lancement de la commande DBCC DBREINDEX.