"Michael Gold" <michael.gold@gmail.com> wrote in message
news:1193263662.114330.132080@v23g2000prn.googlegroups.com...
> 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
>

On Oct 24, 11:41 pm, "Hilary Cotter" <hilary.cot...@gmail.com> wrote:
> When doing a big delete you should set change tracking to scheduled mode
> instead of index in background. You might also find that a full-population
> might work faster than change tracking. This will stop/minimize your locking
> problems.
>
> I am not sure what is causing the timeouts. It could be change tracking. Run
> profiler to see if you can determine what is causing the problem.
>
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
>
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"Michael Gold" <michael.g...@gmail.com> wrote in message
>
> news:1193263662.114330.132080@v23g2000prn.googlegroups.com...
>
> > 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

"Michael Gold" <michael.gold@gmail.com> wrote in message
news:1193317246.250946.286000@o38g2000hse.googlegroups.com...
> On Oct 24, 11:41 pm, "Hilary Cotter" <hilary.cot...@gmail.com> wrote:
>> When doing a big delete you should set change tracking to scheduled mode
>> instead of index in background. You might also find that a
>> full-population
>> might work faster than change tracking. This will stop/minimize your
>> locking
>> problems.
>>
>> I am not sure what is causing the timeouts. It could be change tracking.
>> Run
>> profiler to see if you can determine what is causing the problem.
>>
>> --
>> RelevantNoise.com - dedicated to mining blogs for business intelligence.
>>
>> Looking for a SQL Server replication
>> book?http://www.nwsu.com/0974973602.html
>>
>> Looking for a FAQ on Indexing Services/SQL
>> FTShttp://www.indexserverfaq.com"Michael Gold" <michael.g...@gmail.com>
>> wrote in message
>>
>> news:1193263662.114330.132080@v23g2000prn.googlegroups.com...
>>
>> > 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
>
> Hilary,
>
> Thanks for the suggestions. I actually mispoke when I said we have
> change tracking set to manual. It is actually disabled, and we do a
> full re-indexing every night.
>