Performance - Fragmentation des indexes

Introduction
Lorsque SQL serveur est en perte de performance, et que cette dernière perte de performance est associée a de nombreux Wait Time PAGEIOLATCH_* ou CX_PACKET, il convient de vérifier la fragmentation des indexes.

En effet, s'il n'y a pas de plan de maintenance pour faire une mise à jour des statitiques et une réorganisation des indexes de façon régulière, alors dans ce cas, les indexes seront de plus en plus fragmentés.
La conséquence la plus immédiate pour Sql Serveur est la sélection d'un mauvais "Execution Plan" par le query optimizer... ayant généralement comme résultat une forte augmentation des accès disques et par conséquent de faible performances Sql.

Le disque devient alors le goulot d'étranglement diminuant les performances SQL, la raison pour laquelle les Wait Time PAGEIOLATCH_*  grimpent en flèche.

Reorganisation ou Reconstruire
Reconstruction
Si l'index est fort fragmenté (>30%), il faut le reconstruire pour minimiser la fragmentation.
Reconstruire un index fait une mise-à-jour des statistiques... mais cela n'est possible "online" que pour les versions Enterprise Edition.
La reconstruction (rebuild) étant grande consommatrice de I/O on veillera a effectuer cette operation en heure creuse, même pour une Edition Enterprise.
Reorganisation
Si l'index est peu fragmenté (5% < frag% <= 30%), une simple réorganisation est suffisante. Cela consomme d'ailleurs peu de ressource et peut toujours être exécuté Online.
Par contre, cette opération ne fait pas de mise-à-jour des statistiques, il faudra donc le faire manuellement.


Détection et correction
La requête suivante examine la fragmentation des indexes et en affiche le résultat sous forme de commentaire Sql.
La requête prépare également les différentes requêtes SQL nécessaires pour corriger la situation.

Attention, l'exécution de cette requête consomme ENORMEMENT de ressources disques.

-- Ensure a USE <databasename> statement has been executed first. 
SET NOCOUNT ON; 
DECLARE @objectid int; 
DECLARE @indexid int; 
DECLARE @partitioncount bigint; 
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint; 
DECLARE @partitions bigint; 
DECLARE @frag float; 
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names. 
SELECT object_id AS objectid, index_id AS indexid, 
  partition_number AS partitionnum, 
  avg_fragmentation_in_percent AS frag 
INTO #work_to_do 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') 
WHERE 
  avg_fragmentation_in_percent > 10.0 AND 
  index_id > 0; 

-- Declare the cursor for the list of partitions to be processed. 
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; 

-- Open the cursor. 
OPEN partitions; 

-- Loop through the partitions. 
WHILE (1=1) 
  BEGIN; 
    FETCH NEXT 
      FROM partitions 
      INTO @objectid, @indexid, @partitionnum, @frag; 
    IF @@FETCH_STATUS < 0 BREAK; 
    SELECT @objectname = QUOTENAME(o.name), 
       @schemaname = QUOTENAME(s.name) 
    FROM sys.objects AS o 
      JOIN sys.schemas as s ON s.schema_id = o.schema_id 
    WHERE o.object_id = @objectid; 

    SELECT @indexname = QUOTENAME(name) 
    FROM sys.indexes 
    WHERE object_id = @objectid AND 
      index_id = @indexid; 

    SELECT @partitioncount = count (*) 
    FROM sys.partitions 
    WHERE object_id = @objectid AND index_id = @indexid;

    print '-- Object '+@schemaname + N'.' + @objectname + ' - index '+@indexname+' - Fragmentation '+Cast( @frag as Varchar(10) )
    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. 
    IF @frag < 30.0 
       SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + 
           @schemaname + N'.' + @objectname + N' REORGANIZE'; 
    IF @frag >= 30.0 
       SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + 
           @schemaname + N'.' + @objectname + N' REBUILD'; 

    IF @partitioncount > 1 
       SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); 

    -- Uncomment following lines to execute
    --
    -- EXEC (@command); 
    --PRINT N'Executed: ' + @command; 

    -- print command to execute
    PRINT  @command;
    PRINT  'GO;' 

  END; 
  -- Close and deallocate the cursor. 

CLOSE partitions; 
DEALLOCATE partitions; 

-- Drop the temporary table. 
DROP TABLE #work_to_do; 

GO

Autre ressources

• Index Defrag Script est un script très complet avec plein d'options permettant de faire un traitement très pointu de défragmentation des indexes.