Crippled performance when inserting to fulltext-index table on Sql Server 2005

Question

I'm trying to a big insert of records into a table that is fulltext indexed. I have change tracking set to auto.

The records are inserted from another table, in numbers typically about 50,000 at a time. On SQL Server 2008 this takes something like 5 seconds to complete. But running on our live 2005 environment this takes upwards of 10 minutes.

Looking at the execution plans, it seems to be inserting into the clustered index of the fulltext tracking tables that is the problem. The primary key column is a uniqueidentifier allocated with newsequentialid(). But while 2008 does 1 Clustered Index Merge on fulltext_index_docidstatus, 2005 does a Clustered Index Insert 50,000 times on fulltext_index_map.

Unfortunately, upgrading the server isn't an option right now. Turning off change tracking fixes the problem, but I really don't want to do this as managing population ourselves isn't going to be nice. Change tracking seems to be the fastest way to get new records showing up in the full-text index and that's a priority.

Is there a way to get round this while keeping change tracking enabled?

Answer 1

I suspect that the true nature of your problem is the fact that the full text engine was moved inside of SQL Server in 2008 while previous versions used the external MSFTESQL service. What this means is that SQL 2008 can take full advantage of set-based operations when working with fulltext indexes while, as you've seen, 2005 must treat each row individually. More details can be found in this Microsoft White Paper. Bottom line is that I think you'll have to turn off change tracking to acheive the performance you're seeking or live with the long insert time.