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:
The DBA has suggested the following fixes for this issue, in chronological order:
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, 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