Delay when using full-text search in SQL Server

Question

I decided to change the search with LIKE '%' + @searchTerm + '%' to use the FULLTEXT index. I am using SQL Server 2005.

I created a directory, for example:

 CREATE FULLTEXT CATALOG CatalogName AS DEFAULT 

I created an index, for example:

 CREATE FULLTEXT INDEX ON Table (col1, col2) KEY INDEX TablePK WITH CHANGE_TRACKING AUTO 

And then I use it like this:

 SELECT col1, col2 FROM Table t INNER JOIN FREETEXTTABLE(Table, *, @SearchTerm) s ON s.[Key] = t.Id ORDER BY s.[Rank] DESC 

It returns the correct results, but it takes about 30 seconds to start the search if it has not started for a while. Once it is launched once, all further searches will be instant. If I leave this alone and return in an hour, the first search will be slow. This happens even if the table is not updated in the meantime.

I tried this in two completely different databases, on completely different servers, and the behavior is the same. In one of these databases, the indexed table is very small (50 rows), and on the other a little more (1000 rows).

Can someone help me figure out what the problem is and how to solve it? My only alternative is to abandon FULLTEXT altogether or make a service to run a search query every n minutes!

Answer 1

By default, SQL Server unloads a word breaker after a period of inactivity ( 300 seconds, AFAIR ).

To reboot, verification of the signature is required, requiring an Internet request to the issuer of the certificate.

Run this on the dev server:

 EXEC sp_fulltext_service @action = 'verify_signature', @value = 0 

and see if it helps.

Update:

http://support.microsoft.com/kb/915850/en-us

Answer 2

Your database may be configured to close automatically.

Answer 3

Is the server really busy or has enough memory? I don’t know much about how full-text search works, but this can lead to a lot of actions on the disk if there is a lot of activity on the server.