SQL 2008 fulltext index population delay

Question

My manager is saying that there may be some time before a full text search index is updated after the underlying table data has changed.

For example, if I have a table Products with a column Description and I update that description, then it could take some time before I can search on that new description. Is that true? How long can this take? And is this improved in SQL 2008?

If a user modifies a description we require that a subsequent search should be searching that modified data, not stale data from before the change.

What is the proper/recommended code pattern for achieving this? Should we wait for the index to signal it has completed? If so what guarantees are made regarding when that index update will be completed by, e.g. could it be delayed by minutes in a busy system (pushed back on some queue, versus processed with priority over other commands arriving at the database).

Kalong

Full-Text Index Population describes the type of populations that can occur against an FT index. None of them support an immediate update, and no time bounds are given. – Hillell

Do you have any real time experience in how fast it is generaly? Is it like a few seconds, or more like minutes or hours before I can search on new products? – Kalong

I've seen it take seconds usually, but it depends on your IO subsystem, how many tables, columns, and the size of the data being dealt with, I'd have thought, so probably only answerable on your own systems under typical/worst case load conditions. – Hillell

Answer 1

full text index created on any table get updated every time when you insert any value in that time,if table is not updated than full text index not get rebuild

Yancey

Answer 2

If you are making massive inserts or updates they may trigger long delays in FTI update. Here is some code for delaying your code until FTI catalog state is idle:

DECLARE @CatalogName VARCHAR(MAX)
SET  @CatalogName = 'Your FTI catalog name'
DECLARE @status int
SET @status = 1 --non-idle FTI see below
WHILE @status > 0 --HERE WE LOOP UNTIL FTI STATUS IS IDLE
begin
    SELECT
    @status = FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
    FROM sys.fulltext_catalogs AS cat
    BEGIN
        waitfor  delay '00:00:05';
        PRINT @status
   END

END

For reference see also this thread: How can I know when SQL Full Text Index Population is finished?

Pascoe