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.

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