full-text : first request is very slow

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Noro - 17 May 2006 17:20 GMT

Hello,

I develop an ASP.NET application which is connected to SQL 2005.

I encounter a problem, it is that always the first request that I sending on
fields contained in a full-text index is extremely slow and always exceed
the 30 seconds; the requests which follow are very fast (1 second).

Requests which do not use full-text index are fast as of first request.

I could not detect the source of the problem; if somebody has an idea or
already encountered this problem!

Thank you in advance

Noro

Reply to this Message

Hilary Cotter - 17 May 2006 17:55 GMT

This is probably due to the caching of the catalog files. They have to be
brought into ram. Do you have memory pressures on this server?

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hello,
>
[quoted text clipped - 12 lines]
>
> Noro

Reply to this Message

Noro - 17 May 2006 20:50 GMT

Hi Hilary,

Thank you for your answer.

On my Test Server, I have 1Go RAM and during my tests I checked that there
were only 300 to 350 Mo used. However I have always the same problem with
the first request with each restarting of SQL Server or after certain
duration of inaccessibility to the DB.

Is there an option for charging the full text catalog in RAM with the
starting of SQL so that it is ready as of the first request?

Any idea?

> This is probably due to the caching of the catalog files. They have to be
> brought into ram. Do you have memory pressures on this server?
[quoted text clipped - 16 lines]
>>
>> Noro

Reply to this Message

Hilary Cotter - 18 May 2006 12:39 GMT

AFAIK - No, but you might want to set resource_usage to 5
(sp_fulltext_Service 'resource_usage',5).

You could also have a job run on startup which will issue a full-text query.

Signature

> Hi Hilary,
>
[quoted text clipped - 30 lines]
>>>
>>> Noro

Reply to this Message

Noro - 22 May 2006 10:46 GMT

Thanks for your help.

I planned a work which starts each 10 minutes and contains a full-text
request. Now my first request is fast.

Noro.

> AFAIK - No, but you might want to set resource_usage to 5
> (sp_fulltext_Service 'resource_usage',5).
[quoted text clipped - 36 lines]
>>>>
>>>> Noro

Reply to this Message

Dave Poole - 26 May 2006 20:52 GMT

The reason for this could be because your system does not have access to the
internet. On loading of wordbreakers/filters the mssearch process will try
to validate the signature, it then tries to check for signature revokation
which involves checking a service on the internet. This has a timeout
(about 45 seconds I believe). Once the dll is loaded it will not check
again.

This could be why you see the large response time the first time you issue a
query.

If no queries run for some time (I think it is 30 minutes) the resources
will be unloaded to save memory, next time a query comes along it will need
to go through the validation phase again.

Your task running a fulltext query every 10 minutes should get around this
problem

Signature

Dave Poole
SQL Server Fulltext Team

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

> Thanks for your help.
>
[quoted text clipped - 44 lines]
>>>>>
>>>>> Noro

Reply to this Message