Diretrizes para manutenção do índice de texto completo

Não consegui encontrar nenhum bom recurso on-line, então fiz mais algumas pesquisas práticas e achei que seria útil postar o plano de manutenção de texto completo resultante que estamos implementando com base nessa pesquisa.

Nossa heurística para determinar quando a manutenção é necessária

Nosso principal objetivo é manter o desempenho consistente da consulta de texto completo à medida que os dados evoluem nas tabelas subjacentes. No entanto, por vários motivos, seria difícil lançar um conjunto representativo de consultas de texto completo em cada um de nossos bancos de dados todas as noites e usar o desempenho dessas consultas para determinar quando a manutenção é necessária. Portanto, procuramos criar regras práticas que possam ser calculadas muito rapidamente e usadas como uma heurística para indicar que a manutenção do índice de texto completo pode ser garantida.

Durante essa exploração, descobrimos que o catálogo do sistema fornece muitas informações sobre como qualquer índice de texto completo é dividido em fragmentos. No entanto, não há nenhum "% de fragmentação" oficial calculado (como há para índices de árvore b via sys.dm_db_index_physical_stats ). Com base nas informações do fragmento de texto completo, decidimos calcular nossa própria "% de fragmentação de texto completo". Em seguida, usamos um servidor de desenvolvimento para fazer repetidamente atualizações aleatórias de qualquer lugar entre 100 e 25.000 linhas por vez para uma cópia de 10 milhões de linhas de dados de produção, registrar a fragmentação de texto completo e executar uma consulta de texto completo de benchmark usando CONTAINSTABLE.

Os resultados, conforme vistos nos gráficos acima e abaixo, foram muito esclarecedores e mostraram que a medida de fragmentação que criamos está altamente correlacionada com o desempenho observado. Como isso também está de acordo com nossas observações qualitativas na produção, isso é suficiente para nos sentirmos confortáveis ​​usando a % de fragmentação como nossa heurística para decidir quando nossos índices de texto completo precisam de manutenção.

O plano de manutenção

Decidimos usar o código a seguir para calcular uma % de fragmentação para cada índice de texto completo. Quaisquer índices de texto completo de tamanho não trivial com fragmentação de pelo menos 10% serão sinalizados para serem reconstruídos por nossa manutenção noturna.

-- Compute fragmentation information for all full-text indexes on the database
SELECT c.fulltext_catalog_id, c.name AS fulltext_catalog_name, i.change_tracking_state,
    i.object_id, OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS object_name,
    f.num_fragments, f.fulltext_mb, f.largest_fragment_mb,
    100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0) AS fulltext_fragmentation_in_percent
INTO #fulltextFragmentationDetails
FROM sys.fulltext_catalogs c
JOIN sys.fulltext_indexes i
    ON i.fulltext_catalog_id = c.fulltext_catalog_id
JOIN (
    -- Compute fragment data for each table with a full-text index
    SELECT table_id,
        COUNT(*) AS num_fragments,
        CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_mb,
        CONVERT(DECIMAL(9,2), MAX(data_size/(1024.*1024.))) AS largest_fragment_mb
    FROM sys.fulltext_index_fragments
    GROUP BY table_id
) f
    ON f.table_id = i.object_id

-- Apply a basic heuristic to determine any full-text indexes that are "too fragmented"
-- We have chosen the 10% threshold based on performance benchmarking on our own data
-- Our over-night maintenance will then drop and re-create any such indexes
SELECT *
FROM #fulltextFragmentationDetails
WHERE fulltext_fragmentation_in_percent >= 10
    AND fulltext_mb >= 1 -- No need to bother with indexes of trivial size

Essas consultas geram resultados como os seguintes e, nesse caso, as linhas 1, 6 e 9 seriam marcadas como muito fragmentadas para desempenho ideal porque o índice de texto completo tem mais de 1 MB e pelo menos 10% de fragmentação.

cadência de manutenção

Já temos uma janela de manutenção noturna e o cálculo da fragmentação é muito barato de calcular. Portanto, executaremos essa verificação todas as noites e, em seguida, executaremos apenas a operação mais cara de realmente reconstruir um índice de texto completo quando necessário com base no limite de fragmentação de 10%.

RECONSTRUIR x REORGANIZAR x DROP/CRIAR

REBUILDAs ofertas e opções do SQL Server REORGANIZE, mas estão disponíveis apenas para um catálogo de texto completo (que pode conter qualquer número de índices de texto completo) em sua totalidade. Por motivos herdados, temos um único catálogo de texto completo que contém todos os nossos índices de texto completo. Portanto, optamos por descartar ( DROP FULLTEXT INDEX) e recriar ( CREATE FULLTEXT INDEX) em um nível de índice de texto completo individual.

Pode ser mais ideal dividir os índices de texto completo em catálogos separados de maneira lógica e executar um REBUILD, mas a solução drop/create funcionará para nós enquanto isso.