MS SQL 2005 Express. Full Text search takes rediculously long

[ravil baizhiyenov]

For some reason, FULL TEXT search on my database takes too long to complete the first time. I mean the first time I perform it, it takes about 30 seconds to search the table (which is really small - only some 13 records). However, it then works fine for some time...

I tried using Management Studio, and get same performance issues.

Here is the sql statement I use:

SELECT * FROM Report WHERE FREETEXT([description], 'report')

Please help!

[Paul Sturrock]

Presumably its indexing the table on demand rather than upfront. On a full SQL Server install there is a free text search service that does this in the background, but since Express is not intended as a production database I would guess it doesn't do this. Not sure if this is correct, but you can check if there is a free text service running and if there isn't this may be how it is behaving.

[ravil baizhiyenov]

Thank for you reply!

Yes, the problem was related to the FT service... Apparently after a certain timeout SQL server has to reload the service. And that takes too long... More on this here: http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/699221f0-a850-4f1d-b1db-a067a172f866/

workaround:
sp_fulltext_service 'verify_signature', 0;
GO

Seems to be working fine for now...