maintaining full text index on large table

All we need is an easy explanation of the problem, so here it is.

I’m on SQL Server 2008 and have a table, for reporting purposes, with 500,000 records that will easily reach the millions. The table will employ a full text index for rapid searching
on a handful of columns.

As this is a reporting table and not the source transactional table, sometimes new records will be added, and other times existing records will have to be removed due to changes going on in the source table.

My question is in regards to the best way to build (ongoing) the reporting table and maintain the full text index for this table.

Once the full text index is added, should I:

  1. leave the index alone, and delete/add records as appropriate
  2. leave the index alone, truncate the reporting table, and then insert all appropriate records
  3. other?

I’ve come across these articles so far while researching, but best practice for this scenario is not readily apparent.


How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Full text in SQL Server should have no trouble keeping up with your workload. I currently support a full text index in SQL Sever that is approximately 500,000 rows with 125,000 rows being inserted and deleted everyday. Query load peaks at around 200 full text searches/sec. Response time it’s fairly consistent in the .5 to 1.5 sec range.

Recommendations for full text

  • Use auto change tracking. It just works and you don’t have to schedule anything.

  • Create sql agent alerts for full text population errors. Populations can fail (they rarely do). When they do fail you have to resume them manually. Having a sql agent job kick off an “ALTER FULLTEXT INDEX ON myTable RESUME POPULATION” makes it a non-event.

  • Consider trace flag 7646 if your query load and the volume of updates are both really high. This flag is applicable to 2008/2008 R2 (not 2012). It reduces some blocking on internal data structures and was documented in a SharePoint best practices document. (You probably won’t need this)

  • Otherwise, you probably won’t need to do any additional tuning. It typically just works.

Sql 2012 introduced substantial improvements in scalability and performance so upgrade if you can.

As for how to manage the inserts and updates, just update the rows add you would normally. I wouldn’t recommend truncate/reload, but otherwise don’t worry about it.

Method 2

That’s not all that large of a table for full text indexing. When you hit hundreds of millions or billions of rows that where things that getting more complex.

As it is just setup the full text catalog and index to do auto population and auto change tracking (these are the defaults) and you’ll be fine.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply