sql-server – 全文索引维护指南

维护全文索引应该思量哪些指导原则？

我应该从头编写或从头构建全文目次(见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办理方案在此时代对我们有效.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!