我应该重新编写或重新构建全文目录(见BOL)吗？什么是合理的维护节奏？可以使用什么启发式(类似于10％和30％的碎片阈值)来确定何时需要维护？
(下面的所有内容都只是详细说明问题的额外信息,并展示了我迄今为止所考虑的内容.)
额外信息：我最初的研究
b树索引维护有很多资源(例如,this question,Ola Hallengren’s scripts,以及来自其他站点的关于该主题的大量博客文章).但是,我发现这些资源都没有提供维护全文索引的建议或脚本.
有一个Microsoft documentation提到对基表的b树索引进行碎片整理,然后对全文目录执行REORGANIZE可能会提高性能,但它没有涉及任何更具体的建议.
我还发现了this question,但它主要关注变更跟踪(如何将基础表的数据更新传播到全文索引),而不是可以最大化索引效率的定期维护类型.
额外信息：基本性能测试
此SQL Fiddle包含的代码可用于创建具有AUTO更改跟踪的全文索引,并在表中的数据被修改时检查索引的大小和查询性能.当我在生产数据的副本上运行脚本的逻辑时(而不是小提琴中人为制造的数据),这里是我在每个数据修改步骤后看到的结果的摘要：
尽管此脚本中的更新语句相当人为,但这些数据似乎表明定期维护可以获得很多好处.
额外信息：初步想法
我正在考虑创建一个夜间或每周任务.似乎这个任务可以执行REBUILD或REORGANIZE.
因为全文索引可能非常大(数十或数亿行),所以我希望能够检测目录中的索引是否足够碎片以保证REBUILD / REORGANIZE.我有点不清楚启发式对此有什么意义.
解决方法
我们的启发式确定何时需要维护
我们的主要目标是随着基础表中的数据演变而保持一致的全文查询性能.但是,由于各种原因,我们很难每晚为每个数据库启动一个代表性的全文查询套件,并使用这些查询的性能来确定何时需要维护.因此,我们希望创建可以非常快速地计算的经验法则,并将其用作启发式方法,以指示可以保证全文索引维护.
在此探索过程中,我们发现系统目录提供了有关如何将任何给定的全文索引划分为片段的大量信息.但是,没有官方的“碎片百分比”计算(因为有通过sys.dm_db_index_physical_stats的b树索引).基于全文片段信息,我们决定计算自己的“全文碎片％”.然后,我们使用开发服务器重复进行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,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％碎片阈值执行实际重建全文索引的更昂贵操作.
REBUILD vs. REORGANIZE vs. DROP / CREATE
sql Server提供REBUILD和REORGANIZE选项,但它们仅适用于全文目录(可能包含任意数量的全文索引).由于遗留原因,我们有一个包含所有全文索引的全文目录.因此,我们选择删除(DROP FULLTEXT INDEX),然后在单个全文索引级别上重新创建(CREATE FULLTEXT INDEX).
以逻辑方式将全文索引分解为单独的目录并执行REBUILD可能更为理想,但是drop / create解决方案在此期间对我们有用.
