The SQL Server Development Team at Microsoft has been doing some great work with Integrated Full Text Search (iFTS) in SQL Server 2008. The iFTS feature is available in the February CTP of SQL Server 2008.
What I have seen with iFTS on CTP6 is that it does perform much better than SQL 2005 FTS (at least for our workload), but there are a few issues to be aware of with this CTP. First, (especially if you have a heavy insert/delete load on your base table), you should use manual change tracking instead of automatic change tracking. If you do this, you should have an Agent job that runs UPDATE POPULATION periodically (based on your business requirements.) You can monitor the number of pending changes to help figure out how often you should run the update population.
You should also monitor the number of rows in sys.fulltext_index_fragments. If it starts to go up, you should periodically run an ALTER FULLTEXT CATALOG … REORGANIZE to do a manual merge of the fragments. Otherwise, your fulltext search performance will deteriorate.
The commands below have proven pretty useful in managing and monitoring iFTS.
— Some Useful Full Text Search Commands
— Set Change Tracking to Manual
ALTER FULLTEXT INDEX ON CurrentPostFullText SET CHANGE_TRACKING MANUAL
— Start a Manual Update of the FullText Catalog
ALTER FULLTEXT INDEX ON dbo.CurrentPostFullText START UPDATE POPULATION
— Find out how many changes are pending
SELECT OBJECTPROPERTY(OBJECT_ID(‘CurrentPostFullText’), ‘TableFulltextPendingChanges’) AS ‘Full Text Pending Changes’
— Check Full Text Fragments (lower number of rows is better, closed fragments are bad)
— Status Codes
— 0 = Newly created and not yet used
— 1 = Being used for insert
— 4 = Closed. Ready for query
— 6 = Being used for merge input and ready for query
— 8 = Marked for deletion. Will not be used for query and merge source.
SELECT table_id, fragment_id, fragment_object_id, [timestamp], [status], data_size, row_count
— Do a Manual Merge to eliminate fragments
ALTER FULLTEXT CATALOG ctCurrentPostFullText REORGANIZE
— Rebuild the FT Index completely (may take some time)
ALTER FULLTEXT CATALOG ctCurrentPostFullText
REBUILD WITH ACCENT_SENSITIVITY=OFF;
— Check Accent sensitivity of the FT Catalog
SELECT FULLTEXTCATALOGPROPERTY(‘ctCurrentPostFullText’, ‘AccentSensitivity’);
— Number of full-text indexed items currently in the full-text catalog
SELECT FULLTEXTCATALOGPROPERTY(‘ctCurrentPostFullText’, ‘ItemCount’);
— Size of the full-text catalog in megabytes
SELECT FULLTEXTCATALOGPROPERTY(‘ctCurrentPostFullText’, ‘IndexSize’);
— Active FTS Catalogs
— Outstanding FTS batches
— FTS Index Population