x

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?
more ▼

asked Aug 02, 2010 at 03:56 AM in Default

fiend gravatar image

fiend
3 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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][1]. 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.

[1]: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Filtered-indexes-and-filtered-stats-might-become-seriously-out-of-date.aspx
more ▼

answered Aug 02, 2010 at 05:24 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x1846
x46
x9

asked: Aug 02, 2010 at 03:56 AM

Seen: 2106 times

Last Updated: Aug 02, 2010 at 03:56 AM