SQL Server 2008 Full Text slowness

Posted on September 16, 2008


I was lucky enough to visit Microsoft during the CTP period and was testing out integrated full text search in 2008. An issue we experienced was that full text can be slow when there is a high number of updates to the index and is caused by blocking on the docidfilter internal table. 

It appears to happen if:

  • You have AUTO tracking on your full text indexes; although we changed it to manual and still had this issue.
  • You experience Full-text queries taking a long time to execute; normally when updates are happening at the same time so you might only see this in production.
  • One or more of your queries are complicated or take some time to complete.

You can check to see if your system is slow due to this by:

  • SELECT * FROM sys.dm_os_wait_stats statement , it shows very high wait times some of the locks.
  • Running Sp_who2; it should consistently show that the full-text gather is blocking full-text queries and, in turn, is being blocked by the queries.

The current work around for this issue is to use a global trace flag microsoft kindly enabled on the RTM build.  To use this, type the following Transact-SQL statement:

DBCC TRACEON (7646, -1)

This has a minor side-effect.. which may or may not be important in your organisation. Certainly in ours, it is not. Your full text result may be ever so slightly out of date – e.g. you might return or not return a particular document from the index. However, this issue relates to the dirtyness of an index in terms of 10’s of milliseconds. Important? Unlikely….

I believe there is a knowledge base article on this.