A Few Useful Queries for SQL Server 2008 Integrated Full Text Search (iFTS)

I had a hardware issue this past weekend on one of my database servers where I am running SQL Server 2008 Integrated Full Text Search (iFTS). After the issue was resolved, I noticed a problem with iFTS, where changed items in the base relational tables were not being added to their respective full text indexes.

The way that I noticed this was by running a query like this:

-- Find out how many changes are pending in the Full Text index
SELECT OBJECTPROPERTY(OBJECT_ID('CurrentPostFullTextThursday'), 'TableFulltextPendingChanges') 
AS [Full Text Pending Changes];

If you are running with automatic change tracking, this query should return a result pretty close to zero (unless there is a problem). If you see a high value that does not go down after multiple queries, then you have a problem.

The first thing to try is this command:

-- Resume population in case of an error during manual or auto population

More often than not, you should see the number of pending changes start to go down pretty quickly (depending on your hardware and workload). Full Text crawls tend to be I/O and memory dependent.  After some period of time, you should see the number of pending changes go down to near zero.

I also periodically run this query, to check the number of full text fragments in the entire database:

-- Check Full Text Fragments for entire database (lower number of rows is better)
-- 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 OBJECT_NAME(table_id) AS [Table Name], fragment_id, fragment_object_id, 
       [status], data_size, row_count 
FROM sys.fulltext_index_fragments WITH (NOLOCK);

The number of fragments will vary based on how volatile your base relational data is. You will get better Full Text search performance when you have a lower number of fragments, but maintaining a lower number of fragments puts extra stress on your system during Full Text crawls. If you see a very high number of fragments for a particular table, you can run a manual merge (passing in the name of the full text catalog, not the base table).

-- Start a Manual Merge (when fragment count is high)

Doing a Manual Merge can be very resource intensive, so it is best to do it during off-peak times if possible.

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s