SQL Server FT Timeouts

I have been plagued by an intermittent timeout issue with SQL Server full-text search for some time now. Throughout this time, I have liased with a DBA and tried various things to get it working consistently, but all have failed.

Here's what I know:

sometimes FT queries will take so long as to time out. I get a SqlException with timeout message in my application server when this occurs
when the problem manifests itself, it can be "fixed" by updating stats on the database
stats are updated automatically every week on Sundays, but it's not necessarily late in the week when the problem rears its ugly head
it is not related to verification of the FT engine because we had this disabled when using 2005, and we still get the timeout issues in 2008 (which has the FT engine embedded, thus negating the need for verification)
I cannot for the life of me reliably reproduce the issue. I tried taking a backup of a database when it was known to be exhibiting the issue and then restoring it in my DEV environment. It worked fine right off the bat

The DBA has suggested the following fixes for this issue, in chronological order:

removing a redundant index and using the clustered index for the FT index
using exec instead of sp_executesql. The DBA noticed that the latter was aggressively using a cached plan, but the former would not. I'm not sure why this is or whether it was a good idea to try and do this, but I couldn't anyway because it is the SQL Server ADO.NET client that prepends the SQL with sp_executesql
create further statistics on the FT table that would apparently help the optimizer choose a better path
suffix all full text queries with OPTION (RECOMPILE) so as to force SQL Server to recompile the queries. I had to implement an NHibernate interceptor to do this
change IN clauses with a FT subquery to INNER JOINs. Apparently the combination of OPTION (RECOMPILE) and IN clauses was causing the recompile to take forever. I wasn't able to do this everywhere due to it being too much work when using NHibernate, but I did it in one place

None of these attempts to rectify or work around the problem has worked, and I am at my wit's end.

Can anyone provide any guidance that will help us rectify this issue once and for all?

asked Aug 02 '10 at 03:56 AM in Default

fiend
3 ● 2 ● 2 ● 3

(comments are locked)

1 answer:

fiend, you state that the problem is fixed by updating stats. You also state that a DB-Restore also shows no problems (where the cache is empty and a possibly better plan is generated).

This leads me to believe that you have a FT Index on a table that is getting quite a few updates/inserts to it, but not enough to trigger a stats refresh on the server (is the auto update stats set on this DB?). This is possible if the table with the FT index is large enough, as SQL Server will trigger a stats update when you have updated 20% of a columns data on the whole: see Kim Tripp for a quick explanation. She is talking about filtered indexes, but the explanation of 20% is still valid.

Please try some proactive index/statistics maintenance. I would guess that once a week on Sunday is just not enough for your system. Do you have maintenance windows that would allow for index reorgs/rebuilds or stats recomputes other than Sunday?

Do you have hotspots in the week where data is changed the most? If you identify that Tuesday afternoon is a time of high updates/inserts, it may be prudent to perform your maintenance on Tuesday evening, so the stats are clean for Wednesday.

answered Aug 02 '10 at 05:24 AM