SQL Server 2005 Full Text Search Timeout

  • Article

Setup

  • ASP.Net 3.5 application with a SQL Server 2005 backend
  • Full Text Search index (to search a document library stored in the database)
  • Web frontend to the document search
  • Search is handled by a stored procedure

Symptoms

  • Running a query first thing in the morning results in a SQL timeout
  • Subsequent queries are very fast
  • Any query run after a delay times out
  • Re-compiling the stored proc every few minutes prevents the timeouts

Solution

We initially suspected that the timeout was due to the changing index somehow invalidating the execution plan for the stored proc.  As it turned out, the real reason was something much stranger.  Apparently the Internet connection for our DB server didn’t allow SQL Server to check the certificate revocation of the full text word breaker (as described in this KB Article).  Unlike the scenario described in the article, the DB server does have an Internet connection, but it needs to go through a firewall.

Even so, the solution in the KB worked for us, we just added this line to our SQL scripts:

 sp_fulltext_service 'verify_signature', 0;