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:
- leave the index alone, and delete/add records as appropriate
- leave the index alone, truncate the reporting table, and then insert all appropriate records
- other?
I've come across these articles so far while researching, but best practice for this scenario is not readily apparent.
- https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search?redirectedfrom=MSDN&view=sql-server-ver15
- https://techcommunity.microsoft.com/t5/datacat/best-practices-for-integrated-full-text-search-ifts-in-sql-2008/ba-p/305000
- https://social.msdn.microsoft.com/Forums/en-US/c743f6e4-88c6-478b-954b-ba8207e391c5/sql-2005-full-text-index-rebuild-nightly-best-approach?forum=sqldatabaseengine
Best Answer
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.