Delay in using fulltext search in SQL Server

Question

I have decided to change a search from using LIKE '%' + @searchTerm + '%' to use a FULLTEXT index. I'm using SQL Server 2005.

I've created a catalog, like so:

CREATE FULLTEXT CATALOG CatalogName AS DEFAULT

I've created the index, like so:

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

And then I use it like so:

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 run the search if it hasn't run for a while. After it has run once, all further searches are instant. If I leave it alone and come back in an hour, the first search is slow again. It seems to happen even if the table is not updated in the meantime.

I've tried this on two completely different databases, on completely different servers, and the behaviour is the same. On one of these databases, the table being indexed is very small (50 rows), and on the other it is a little larger (1000 rows).

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

Answer 1

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

Reloading it requires checking the signature which requires an Internet request to the certificate issuer.

Please 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