Full Text System Views

Posted on April 20, 2008

0


Here is a summary of the new Full Text System Views in SQL 2008.

sys.fulltext_stoplists

A list of STOPLISTS in your database. STOPLISTS are database specific. STOPLISTS replace Noise Word Files.

sys.fulltext_stopwords

This will show you you’re STOPWORDS. You can filter this on the language and or stoplist of your choice. STOPWORDS are individual words that make up a STOPLIST and replace Noise Words in previous versions of SQL Server.

For more information on STOPLISTS, check out my SQL Agent’s post at:

http://sqlagents.wordpress.com/2008/04/20/noise-words-have-been-silenced/.

sys.fulltext_index_fragments

This is a great view; this will display a row for each full-text index fragment. I have posted before about this but basically if you see that you have between 30-50 fragments in a table you should be thinking of reorganizing (REORGANIZE) your fulltext index.

For a clear picture, use this query:

SELECT
OBJECT_NAME([table_id])
AS TableName,
COUNT([fragment_id])
AS Fragments
FROM

sys.fulltext_index_fragments
GROUP

BY
OBJECT_NAME([table_id])
HAVING

COUNT([fragment_id])
>=30

Advertisements