SQL 2005 FT Issues (mass deletes blocked/slow, Queries against index stop working)

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi All,

We are experiencing a couple of strange issues with FTI/FTS.

Our environment: W2K3x64 Clustered SQL 2005 SP 1(2 instances) 4GB Ram,
Dual Proc, lots of disk.
We have one table (currently 425,000 rows) that has a FTI on 6
columns, all VARCHAR columns. Change tracking is set to Manual, we
update the index fully every morning at 1AM.

It works great most of the time, but some times queries that call a
stored proc that do a select against this index (which do a select ...
where contains(*, <string>)) time out from our ASP.NET applications,
but not from SQL Management studio. When this happens if we either
fully rebuild the index or recompile the stored procedure the ASP.NET
apps are again able to get results.

The Second issue we are seeing is that when we do mass deletes from
this table (90,000 rows using a clustered index) we sometimes get
blocking on the fulltext indexes and a query that used to take 2
seconds takes 20 minutes.

When both of these issues occur, the Processing on the SQL Server box
(which is normally under 5%) stays between 50%-60%. Memory stays
relatively constant ~2 GB used by SQL Server.

Does anyone have any thoughts on what is going on? When doing a big
delete against a table that has an FTI, is there some best practices
we should be following? We don't have that much experience with FTI/
FTS so I'm hoping we are missing something obvious.

Thanks in advance.

Mike

.