SQL Server 2005 FREETEXT()性能问题

Question

我有一个查询，其中包含大约6-7个连接表和一个FREETEXT()谓词，该谓词位于where中基表的6列上。

现在，这个查询在过去一年中工作得很好(不到2秒)，并且几乎保持不变(我尝试了旧版本，但问题仍然存在)。

因此，今天，突然之间，同样的查询大约需要1-1.5分钟。

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

我暂时将查询切换为使用LIKE，直到我弄清楚这一点(现在大约需要6秒)。

当我在查询性能分析器中查看查询时，当我比较“FREETEXT”查询和“LIKE”查询时，前者的读取次数是后者的350倍(4921261比13943)，CPU使用率是后者的20倍(38937比1938)。

因此，它实际上是“FREETEXT”谓词导致它如此缓慢。

有没有人知道原因是什么？或者我可以做进一步的测试？

编辑

好吧，我只是再次运行查询来获取执行计划，现在又需要2-5秒，没有任何更改，尽管问题昨天仍然存在。这不是由于任何外部因素，因为当我在上周四第一次测试这个问题时，我已经停止了所有访问数据库的应用程序，所以它不是由于任何其他加载。

好吧，我仍然会包括执行计划，尽管现在一切都恢复正常了，但它可能没有太大帮助。注意，这是一个对遗留数据库的巨大查询，我无法更改(例如，规范化数据或删除一些不必要的中间表)

Query plan

好的，这是完整的query

我可能不得不解释它到底是做什么的。基本上，它会获得招聘广告的搜索结果，其中有两种类型的广告，高级广告和普通广告。结果被分页到每页25个结果，10个高级结果在顶部，15个普通结果在这之后，如果足够多的话。

所以有两个内部查询，根据需要选择尽可能多的高级/普通查询(例如，在第10页，它获取前100个高级查询和前150个正常查询)，然后这两个查询与row_number()命令和一些数学交错。然后按行号对组合进行排序，并返回查询。好吧，它在另一个地方被用来获得当前页面所需的25个广告。

哦，整个查询是在一个巨大的遗留Coldfusion文件中构建的，由于它一直运行得很好，到目前为止，我还不敢考虑/更改大部分内容……永远不要接触正在运行的系统等等;)只是一些小事情，比如更改中央where子句的一小部分。

该文件还会生成其他查询，这些查询基本上执行相同的操作，但没有高级/非高级区别以及此查询的许多其他变体，因此我不太确定对其中一个查询的更改会如何更改其他查询。

好的，由于问题没有再次出现，我给了Martin赏金，因为他是到目前为止最有帮助的人，我不想让赏金不必要地过期。感谢其他人的努力，如果再次发生这种情况，我会尝试您的建议:)

Answer 1

出现此问题的原因可能是对全文查询返回的结果数量的基数估计不佳，从而导致联接操作的策略不佳。

如果你把它分成两个步骤，你会发现它的性能如何？

一个新步骤使用全文查询的结果填充临时表或表变量，第二个步骤将现有查询更改为引用临时表。

(注意:您可能希望尝试使用和不使用OPTION(RECOMPILE)的连接，同时查看以下查询计划：(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)

Answer 2

通常，当我们遇到这个问题时，这是因为表碎片和有问题的索引上的陈旧统计数据。

下一次，尝试在重建/重新索引之后执行sp_updatestats。

有关详细信息，请参阅Using Statistics to Improve Query Performance。