Blog Post

A DMV a Day – Day 8

,

We have two related DMVs for Day 8. The first one is sys.dm_fts_active_catalogs, which is described by BOL as:

Returns information on the full-text catalogs that have some population activity in progress on the server.

The second one is sys.dm_fts_index_population which BOL describes as:

Returns information about the full-text index populations currently in progress.

By joining these two together, you get a very useful summary of what is happening with your fulltext catalogs. This query works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Get population status for all FT catalogs in the current database
SELECT c.name, c.[status], c.status_description, OBJECT_NAME(p.table_id) AS [table_name], 
p.population_type_description, p.is_clustered_index_scan, p.status_description, 
p.completion_type_description, p.queued_population_type_description, 
p.start_time, p.range_count 
FROM sys.dm_fts_active_catalogs AS c 
INNER JOIN sys.dm_fts_index_population AS p 
ON c.database_id = p.database_id 
AND c.catalog_id = p.catalog_id 
WHERE c.database_id = DB_ID()
ORDER BY c.name;

Of course, this query is only useful if you are using full text search. In my experience, I have found that not too many DBAs seem to be using full text search in SQL Server. SQL Server 2008 has integrated full text search (iFTS) that is much easier to implement and maintain than the old version of full text search in SQL Server 2005. The new iFTS also performs much better than the 2005 version did, both for index creation and maintenance, and for full text searches.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating