La fragmentation et la défragmentation des index relationnels est un sujet connu sur lequel la littérature ne manque pas. Mais on voit bien moins souvent des lignes sur les index fulltext, donc la fragmentation élevée nuit de la même façon aux performances des requêtes que leurs cousins relationnels.
Voici donc une requête qui permet de mesurer la fragmentation des index fulltext, et qui génère l’instruction de maintenance adéquate, le cas échéant :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | WITH CTE AS ( SELECT C.name AS catalog_name , I.change_tracking_state_desc , S.name + '.' + T.name AS table_name , F.fragment_count , F.index_size_MB , F.largest_fragment_size_MB , F.avg_fragment_MB , CAST(100.0 * (F.index_size_MB - F.avg_fragment_MB) / NULLIF(F.index_size_MB, 0) AS decimal(5,2)) AS avg_MB_frag_pct , CAST(100 - (100.0 / F.fragment_count) AS decimal(5,2)) AS avg_frag_count_pct FROM sys.fulltext_catalogs AS C INNER JOIN sys.fulltext_indexes AS I ON I.fulltext_catalog_id = C.fulltext_catalog_id INNER JOIN ( -- Compute fragment data for each table with a full-text index SELECT table_id , COUNT(*) AS fragment_count , CAST(SUM(data_size / (1024.0 * 1024)) AS decimal(9,2)) AS index_size_MB , CAST(AVG(data_size / (1024.0 * 1024)) AS decimal(9,2)) AS avg_fragment_MB , CAST(MAX(data_size / (1024.0 * 1024)) AS decimal(9,2)) AS largest_fragment_size_MB FROM sys.fulltext_index_fragments GROUP BY table_id ) AS F ON F.table_id = I.object_id INNER JOIN sys.tables AS T ON T.object_id = I.object_id INNER JOIN sys.schemas AS S ON S.schema_id = T.schema_id ) SELECT * , 'ALTER FULLTEXT CATALOG ['+ catalog_name + CASE WHEN avg_frag_count_pct > 30 THEN '] REBUILD' WHEN avg_frag_count_pct BETWEEN 10 AND 30 AND avg_frag_count_pct >= 10 THEN '] REORGANIZE' END AS maintenance_sql FROM CTE |
Bonne maintenance d’index fulltext !