SQL Server 2005 FREETEXT() 性能问题

Question

我有一个查询，涉及6-7个联接表，在where子句中基础表的6列上使用FREETEXT()谓词。过去一年里，这个查询工作得很好（不到2秒），并且实际上一直没有改变过（我尝试了旧版本，问题仍然存在）。但是今天，同样的查询突然需要大约1-1.5分钟的时间。

在检查SQL Server 2005中的执行计划、重建该表的FULLTEXT索引、重新组织FULLTEXT索引、从头创建索引、重新启动SQL Server服务和整个服务器之后，我不知道还能尝试什么。

我暂时将查询切换为使用LIKE，直到我解决这个问题（现在需要大约6秒）。

当我在查询性能分析器中查看查询时，将“FREETEXT”查询与“LIKE”查询进行比较时，前者的读取次数是后者的350倍（4921261 vs. 13943），CPU使用率是后者的20倍（38937 vs. 1938）。

所以确实是“FREETEXT”谓词导致它变得如此缓慢。

有人知道问题的原因吗？或者我还可以做哪些进一步的测试？

[编辑]

好的，我刚刚再次运行查询以获取执行计划，现在它又需要2-5秒才能完成，尽管没有对其进行任何更改，但昨天仍存在问题。并且这不是由于任何外部因素引起的，因为我在上周四首次测试问题时停止了所有访问数据库的应用程序，所以这不是由于任何其他负载引起的。

好的，我仍将包括执行计划，尽管现在一切都正常可能帮助不大...请注意，这是针对我无法更改的旧数据库的巨大查询（即规范数据或摆脱一些不必要的中间表）

查询计划

好的，这里是完整的查询

我可能需要解释一下它究竟是做什么的。基本上，它会获取工作广告的搜索结果，其中有两种类型的广告，高级广告和普通广告。结果分页为每页25个结果，前面有10个高级广告，然后是15个普通广告（如果有足够的话）。

因此，有两个内部查询会选择所需数量的高级/普通广告（例如，在第10页上，它会获取前100个高级广告和前150个普通广告），然后这两个查询与 row_number() 命令和一些数学运算相间。然后根据行号对组合进行排序，并返回查询结果。嗯，它还在另一个地方用于仅获取当前页面所需的25个广告。

哦，整个查询都是在一个巨大的遗留 Coldfusion 文件中构建的，由于一直运行良好，我到目前为止还没有敢大规模更改...永远不要碰已经运行良好的系统等。只是像更改中心 where 子句的一些小东西。

该文件还生成其他查询，这些查询基本上都是相同的，但没有高级/非高级区别，而且还有很多其他变体的这个查询，因此我从未确信对其中之一的更改可能会如何影响其他查询...

好的，由于问题没有再次出现，我授予马丁赏金，因为他到目前为止是最有帮助的，我不想白白浪费赏金。感谢其他人的努力，如果问题再次发生，我会尝试你们的建议 :)

- Zenon

---

你能发布执行计划吗？问题可能沿着Martin建议的方向，如果是这种情况，使用FORCE ORDER重新排列查询可能会有所帮助。 - Mark Storey-Smith

奇怪。我猜想你正在使用之前有问题的相同FreeText搜索词，并且数据中没有发生任何变化（例如存档过程），以致于导致FREETEXT部分匹配记录数量在一夜之间突然减少？ - Martin Smith

目前没有归档过程，但是有些项目的状态会从3设置为4，这意味着它们处于非活动状态（查询仅过滤状态=3的项目），并且有自动导入添加项目。因此，目前可能有大约1200个活动条目，而前几天可能有大约1300个活动条目。但是，并没有从数据库中删除行，如果您所说的“归档器”是指这个（我曾经想要出于性能考虑实现一个归档器，但管理层反对...想象一下9年的用户提交内容在单个表中，大多数都是非活动的 ;)）。 - Zenon

你在问题出现时是否查看了实际计划，以确定实际行数和预估行数之间是否存在重大差异？ - Martin Smith

我当时查看了实际执行计划，但没有查看估算计划。虽然我不是专业的数据库管理员，但除了freetext部分具有最高的CPU成本之外，我没有看到任何异常情况。 - Zenon

Answer 1

这个问题可能是由于全文查询返回结果数量的基数估计不佳，导致JOIN操作策略不佳而引起的。

如果将其分为两个步骤，您如何找到性能？

一个新步骤会使用Full Text查询结果填充临时表或表变量，第二个步骤会更改现有查询以引用临时表。

（注：在查看查询计划时，您可能需要尝试使用和不使用OPTION(RECOMPILE)进行此JOIN，其中（A）返回许多结果的自由文本搜索术语（B）仅返回少量结果。）

编辑 在没有冒犯性的查询的情况下，很难准确说明我的意思，但我的意思是不要执行以下操作

SELECT <col-list>
FROM --Some 6 table Join
WHERE FREETEXT(...);

这个执行起来怎么样？

DECLARE @Table TABLE
(
<pk-col-list>
)
INSERT INTO @Table
SELECT PK
FROM YourTable
WHERE FREETEXT(...)

SELECT <col-list>
FROM --Some 6 table Join including onto @Table
OPTION(RECOMPILE)

- Martin Smith

---

“如果你将性能分成两个步骤，你是什么意思？”在当前的情况下，这对我来说不太合理。 我会在明天的工作中发布执行计划，但这是一个庞大的查询（带有rownumber的分页查询和两个联接子查询以及许多联接），因此肯定有很多可以进行性能调整的地方。但正如我所说的，我想知道为什么在一个月前更改了查询（添加了一个额外的where子句）并且一直工作正常，到4天前突然执行时间变长了近50倍... - Zenon

需要查看查询计划才能猜测。 - Martin Smith

好的，我已经尝试过使用 ´DECLARE @Table´ 和 ´OPTION(RECOMPILE)´，花费了3秒钟，和原本的查询一样。但是当问题再次出现时，我需要进行测试。顺便说一下，当我从查询中删除约束条件以仅搜索活动（status=3）行时，即搜索所有行（大约一百万行）而不是 19,000 行时，查询仍然需要 3 秒钟，因此我怀疑这并不是由于过去3天中某些数据库条目已被设置为非活动状态所导致的。 - Zenon

Answer 2

通常当我们遇到这个问题时，是因为表碎片和相关索引的统计信息已经过期。

下次在重建/重建索引后尝试执行sp_updatestats。

更多信息请参见使用统计信息来提高查询性能。

- GalacticJello

---

是的，这也是可能的，但这并不能解释为什么在使用相同统计信息运行LIKE查询时它能够正常工作。 - Martin Smith

FREETEXT和LIKE使用不同的统计数据，我认为（FREETEXT像远程源一样处理，而LIKE使用实际索引统计数据）。 - GalacticJello

正确。SQL2005中的FREETEXT没有任何可用的统计数据来进行基数估计，也不会受到sp_updatestats的影响（始终假设将返回1行），因此建议将其拆分。所以我想说的是，一个更依赖于统计数据的查询没有遇到同样的问题可能有些奇怪（虽然有可能）。 - Martin Smith