SQLServer 2005: Full Text Search Monitoring

Seems more and more lately that a DBA can't get out of being the Full Text query expert. Whether that is an in-database technology such as Full Text/Intermedia Text, or an external database technology such as google appliances / FAST appliances. The typical DBA is always stuck in between somewhere, and looks like we have to understand at least how to monitor and troubleshoot these indexing technologies.

Here are a few important views/selects for monitoring Full Text Indexes withing SQLServer 2005:

Important dm views:

sys.dm_fts_active_catalogs /* info about the active ft catalogs */
sys.dm_fts_index_population /* population types for the ft ndexes */
sys.dm_fts_population_ranges /* detailed info about the population of ft indexes */

Important system procedures:

sp_help_fulltext_catalogs /* info on names and locations of ft indexes */

sp_help_fulltext_tables /* info on tables and indexes used by ft catalogs */

Example Queries:

/* Get Status of Full Text Indexes Currently Being Populated */

SELECT

DISTINCT DBNAME (database_id),
OBJECT_NAME (table_id)

FROM sys.dm_fts_index_population population
JOIN sys.dm_fts_population_ranges ranges
ON (population.memory_address = ranges.parent_memory_address)
WHERE ranges.session_id <> 0

/* Get List of Full Text Index Population Counts as Compared with Table Counts */

SELECT catalog.name,

OBJECT_NAME (population.table_id) AS table_name,
catalog.is_paused,
catalog.status_description,
catalog.row_count_in_thousands AS ft_index_rows_in_thousands,
tables.row_count / 1000 AS table_rows_in_thousands,
population.population_type_description AS population_type,
population.status_description AS status,
population.start_time

FROM sys.dm_fts_active_catalogs catalog
CROSS JOIN sys.dm_fts_index_population population
INNER JOIN sys.dm_db_partition_stats tables ON population.table_id = object_id
WHERE catalog.database_id = population.database_id
AND catalog.catalog_id = population.catalog_id
AND catalog.database_id =

(SELECT dbid FROM sys.sysdatabases WHERE name = DB_NAME () )

AND tables.index_id = 1;