Integrated Full Text Search (iFTS) in SQL Server 2008

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.

Simon Sabin, who is an active SQL Server MVP in the UK, has a good series of posts talking about performance improvements with iFTS compared to the old FTS in SQL Server 2005.

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
    FROM sys.fulltext_index_fragments

    — 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
    SELECT database_id,catalog_id,memory_address,name,is_paused,[status],status_description,
           previous_status,previous_status_description,worker_count,active_fts_index_count,auto_population_count,
           manual_population_count,full_incremental_population_count,row_count_in_thousands,is_importing
    FROM sys.dm_fts_active_catalogs
   
    — Outstanding FTS batches
    SELECT database_id,catalog_id,table_id,batch_id,memory_address,crawl_memory_address,memregion_memory_address,
           hr_batch,is_retry_batch,retry_hints,retry_hints_description,doc_failed,batch_timestamp
    FROM sys.dm_fts_outstanding_batches
 
    — FTS Index Population
    SELECT database_id,catalog_id,table_id,memory_address,population_type,population_type_description,
         is_clustered_index_scan,range_count,completed_range_count,outstanding_batch_count,[status],
         status_description,completion_type,completion_type_description,worker_count,
         queued_population_type, queued_population_type_description,start_time,incremental_timestamp
    FROM sys.dm_fts_index_population

 

Technorati Tags: SQL Server 2008, iFTS