5

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. https://learn.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search?redirectedfrom=MSDN&view=sql-server-ver15
  2. https://techcommunity.microsoft.com/t5/datacat/best-practices-for-integrated-full-text-search-ifts-in-sql-2008/ba-p/305000
  3. https://social.msdn.microsoft.com/Forums/en-US/c743f6e4-88c6-478b-954b-ba8207e391c5/sql-2005-full-text-index-rebuild-nightly-best-approach?forum=sqldatabaseengine
Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
2
  • I know this is really old. But how would you go about creating a sql agent alert for that full text population error?
    – Prescient
    May 19, 2017 at 1:10
  • @StrayCatDBA I'm interested too in knowing how to create a SQL Server Agent alert on full-text population error. Which alert numbers could be useful ?
    – Jefferson B. Elias
    Jul 27, 2017 at 9:44
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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