SQL

Monday 28 February 2011

Dormant Full Text Index in SQL Server

I recently created a full text index to improve search performance on some large text fields. The resulting performance was great, so I took it to show my boss (an IT "Bruno").

I had to wait a few minutes before I could see him, and then when I fired up my lovely new search page disaster struck in the form of a timeout - making me look like an imbecile.

I covered by showing him something shiny, and hastily hit refresh. The search came back in less than a second, as did each subsequent search. Happy boss.

When I got back to my desk I did another search and got another timeout. It seems my Full Text Index is going to sleep if not used for 5 minutes, and then takes 30 seconds or more to wake up. Rubbish!

So, I have had to set up a job to hit the FTI with a simple query every 5 minutes, keeping it "awake".

Something like this:

SELECT * FROM Dreams WHERE CONTAINS(SubText, 'fear of trombones')

Doesn't feel great having to do that, but now my Full Text Index is always ready for action.

2 comments:

  1. I am running into the same issue. Have you had any resolution other then the timed search?

    ReplyDelete
  2. No, sorry - never found a better solution for my sleepy FTI. But hey, luckily I've got other much worse problems to keep my mind off it :)

    ReplyDelete