Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
1. http://technet.microsoft.com/en-us/library/ms142497.aspx
2. http://sqlcat.com/sqlcat/b/msdnmirror/archive/2008/11/05/best-practices-for-integrated-full-text-search-ifts-in-sql-2008.aspx
3. http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/c743f6e4-88c6-478b-954b-ba8207e391c5

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.