LLI Wiki — My Digital Knowledge Base

SQL Server : contrôler la fragmentation des index

Deux requêtes pour lister la fragmentation des index de la base courante, avec une lecture rapide puis une lecture par échantillon.

Deux requêtes pour contrôler la fragmentation des index de la base courante, à partir de sys.dm_db_index_physical_stats. La première liste la fragmentation de tous les index. La seconde ne lit que le niveau feuille des tables utilisateur, à partir d'un échantillon, et ajoute l'espace utilisé par page, le facteur de remplissage et la taille de l'index.

Fragmentation de tous les index

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

Niveau feuille, par échantillon

SELECT Coalesce(Object_Schema_Name(indexes.object_id) + '.', '')
       + Coalesce(Object_Name(indexes.object_id) + '/', '')
       + Coalesce(indexes.name, 'Heap'),
  Str(avg_fragmentation_in_percent, 10,1) AS [avg_fragmentation_%],
  Str(avg_page_space_used_in_percent, 10,1) AS [avg_page_space_used_%],
  fill_factor,
  Str((avg_record_size_in_bytes * record_count) / (1024.0 * 1024), 10,2) AS [IndexSize_(MB)]
  FROM sys.dm_db_index_physical_stats(Db_Id(), NULL, NULL, NULL, 'Sampled') AS IPS
    INNER JOIN sys.indexes
      ON indexes.index_id = IPS.index_id AND indexes.object_id = IPS.object_id
  WHERE ObjectProperty(indexes.object_id, 'IsUserTable') = 1 AND index_level=0 --leaf level
  ORDER BY  [avg_fragmentation_%] DESC;

Remarques

Mastodon LLIMastodon SocialFosstodon