SQL Server 2008 Full text Search Performance Issue

Question

I recently upgraded from a Win 2003 / SQL Server 2000 (HP ML350 G4p - Dual Xeon with 2GB RAM) to new hardware (IBM x3200 M3 - Single Xeon QC X3450 2.66GHz 10GB RAM) running Win 2008 R2 / SQL Server 2008. We run a classic ASP website and use Full Text Search to enable customers to search our product descriptions.

The search is performed by a stored procedure which builds up a dynamic query to run the search using the contains predicate.

I have an intermittent problem in that some searches are extremely slow. Sometimes taking over a minute to complete. At other times they will complete in less than a second. Didn't have this problem on the old server at all.

I have replicated this problem by running the stored procedure from the SSMS, and again, this can sometimes take an extremely long time.

I suspect the new Full Text Search system within SQL Server 2008, but I'm not sure how to pinpoint the issue. When the timeouts are occurring, there doesn't seem to be a big load on the server cpu. I don't know where else to look. Relatively new to SQL Server 2008.

Change Tracking on the Index if set to Off, and I'm running a job to incrementally update the index each day, as well as optimizing the catalog each day.

Searching around on Google, this seems to be reasonably common a problem, but I haven't found a definitive answer to it yet. Can someone point me in the right direction?

Thanks in Advance, Mike.

Answer 1

In case someone else has this issue, I've finally solved it. I needed to apply Cumulative Update 9 then do the following: –

DBCC TRACEON(4199, -1); GO;

DBCC FREEPROCCACHE; GO;

Found the answer from a post by Forrestsjs on this page: http://social.msdn.microsoft.com/Forums/en-US/sqlsearch/thread/7e45b7e4-2061-4c89-af68-febd668f346c/

Responses have dropped from about 3 minutes to 2-3 seconds for wildcard searches such as: "office 2010 pro*"

Answer 2

Just found this: StackOverflow’s SQL 2008 FTS issue solved (what service pack and/or cumulative updates have you applied?)

Did you rebuild ALL indexes after upgrading from SQL Server 2000 to SQL Server 2008?

You need to.

Run this (but make sure you have your DBA/Manager/whoever's permission before running in production):

exec sp_msforeachtable "DBCC DBREINDEX('?')"
go

exec sp_msforeachtable "UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS"
go

Also, Update usage counters: In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, run DBCC UPDATEUSAGE on all databases following the upgrade.

If that doesn't fix your problem, investigate setting 'Max Degree of Parallelism' to 1.

This post might be helpful: Upgrading from SQL Server 2000 to 2008

The behaviour you describe "Sometimes taking over a minute to complete. At other times they will complete in less than a second" is usually a sign that inappropriate query plans are being cached.

Have you read this?: SQL 2008 Full-Text Search Problems

Do you have a large number of updates occurring?

From this post SQL Server 2008 Full Text slowness :

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)