Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Comments

Posted by Anonymous on 8 April 2010

Pingback from  Dew Drop – April 8, 2010 | Alvin Ashcraft's Morning Dew

Posted by Dukagjin Maloku on 8 April 2010

Haa, what a coincidence, I was setting-up a Full-Text Index in a table today, so with this script I can see everything what I have done  few hours ago! - Thanks for the script!

Posted by Jason Brimhall on 8 April 2010

This could be useful to me on a future project.

Thanks yet again.

Leave a Comment

Please register or log in to leave a comment.