SQLServer 2005: Full Text Issue

In my opinion, there is security, and then there is the absurd paranoia. Microsoft tends to ride this line a little too much lately. Trying to be the end all to security, they forget that we have firewalls, access lists, etc, etc that should be considered when locking things down.

The interesting issue I've run into with Full Text searches now, is , somewhat not surprisingling, a known Microsoft Bug / Feature

The question I'd like to pose Microsoft is this:

If the work break files I've installed came from the CD / Microsoft bits that I got from Microsoft, should I be able to assume that they've verified where they've came from? And for that matter, If I install a third - party work break file after my SQLServer Full Text install, shouldn't it be my responsibility to verify the validity of those files?

Why should Microsoft automatically be checking these for me? (and why, should they do it every time I load the word breaker info into memory?

BTW - if you are having the problem, it presents itself something like this:

Running SQLServer 2005 (or possibly other servers), with Full Text Indexing, you notice that the initial call to the query engine is slow (surprisingly, 45 seconds consistently). Subsequent queries perform as expected. Then after 10 or 15 minutes, the query processing engine appears to 'go to sleep' and you have another 45 second response time for a query to indexing service.

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;