主页 / dba / 问题 / 107808
Accepted
Geoff Patterson
Asked: 2015-07-23 11:48:14 +0800 CST

全文索引维护指南

  • 772

维护全文索引应考虑哪些准则？

我应该重建还是重组全文目录（参见BOL）？什么是合理的维护节奏？哪些启发式方法（类似于 10% 和 30% 碎片阈值）可用于确定何时需要维护？

（下面的所有内容都只是详细说明问题的额外信息，并显示了我到目前为止的想法。）



额外信息：我的初步研究

有很多关于 b-tree 索引维护的资源（例如，这个问题、Ola Hallengren 的脚本，以及来自其他站点的大量关于该主题的博客文章）。但是，我发现这些资源都没有提供维护全文索引的建议或脚本。

有Microsoft 文档提到，对基表的 b 树索引进行碎片整理，然后对全文目录执行 REORGANIZE 可能会提高性能，但它没有涉及任何更具体的建议。

我也发现了这个问题，但它主要关注更改跟踪（对基础表的数据更新如何传播到全文索引中），而不是可以最大限度地提高索引效率的定期维护类型。

额外信息：基本性能测试

此SQL Fiddle包含可用于创建具有更改跟踪的全文索引的代码，AUTO并在修改表中的数据时检查索引的大小和查询性能。当我在生产数据的副本上运行脚本的逻辑时（与小提琴中的人工制造数据相反），以下是我在每个数据修改步骤后看到的结果摘要：

在此处输入图像描述

尽管此脚本中的更新语句相当做作，但这些数据似乎表明定期维护有很多收获。

额外信息：初步想法

我正在考虑创建一个每晚或每周的任务。似乎此任务可以执行 REBUILD 或 REORGANIZE。

因为全文索引可能非常大（数千万或数亿行），所以我希望能够检测到目录中的索引何时足够碎片化，以至于需要进行 REBUILD/REORGANIZE。我有点不清楚启发式方法可能对此有意义。

sql-server full-text-search

1 个回答

  1. Best Answer

    我无法在网上找到任何好的资源，所以我做了更多的动手研究，并认为发布我们正在实施的基于该研究的全文维护计划会很有用。


    我们的启发式方法来确定何时需要维护

    在此处输入图像描述

    我们的主要目标是随着基础表中数据的演变保持一致的全文查询性能。但是，由于各种原因，我们很难每晚针对我们的每个数据库启动一套具有代表性的全文查询，并使用这些查询的性能来确定何时需要维护。因此，我们希望创建可以非常快速地计算并用作启发式的经验法则，以表明可能需要维护全文索引。

    在这个探索过程中，我们发现系统目录提供了大量关于任何给定全文索引如何划分为片段的信息。但是，没有计算出官方的“碎片百分比”（就像通过sys.dm_db_index_physical_stats计算的 b-tree 索引一样）。基于全文碎片信息，我们决定计算自己的“全文碎片百分比”。然后，我们使用开发服务器一次重复随机更新 100 到 25,000 行到 1000 万行的生产数据副本，记录全文碎片，并使用CONTAINSTABLE.

    如上图和下图所示，结果非常有启发性，并表明我们创建的碎片化度量与观察到的性能高度相关。由于这也与我们在生产中的定性观察相联系，这足以让我们习惯于使用碎片百分比作为我们决定何时需要维护全文索引的启发式方法。

    在此处输入图像描述


    维护计划

    我们决定使用以下代码来计算每个全文索引的碎片百分比。任何具有至少 10% 碎片的非平凡大小的全文索引都将被我们的通宵维护标记为重新构建。

    -- 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

    这些查询产生如下结果，在这种情况下，第 1、6 和 9 行将被标记为过于碎片化而无法获得最佳性能，因为全文索引超过 1MB 并且至少有 10% 碎片化。

    在此处输入图像描述


    维护节奏

    我们已经有一个夜间维护窗口，并且碎片计算的计算成本非常低。因此，我们将在每晚运行此检查，然后仅在必要时根据 10% 的碎片阈值执行更昂贵的实际重建全文索引的操作。


    重建与重组与删除/创建

    SQL Server 提供REBUILD和REORGANIZE选项，但它们仅可用于全文目录（可能包含任意数量的全文索引）。由于遗留原因，我们有一个包含所有全文索引的全文目录。因此，我们选择在单个全文索引级别上删除 ( DROP FULLTEXT INDEX)，然后重新创建 ( )。CREATE FULLTEXT INDEX

    以逻辑方式将全文索引分解为单独的目录并执行REBUILD替换可能更理想，但同时删除/创建解决方案将适用于我们。

    • 40

相关问题