Mesurer et remédier à la fragmentation des index fulltext

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 !

Laisser un commentaire