Fulltext Fragmentation (iFTS)

Posted on February 24, 2008

2


One neat feature of SQL Server 2008 is the ability to see how fragmented your fulltext indexes are. By running the following you can see how many fragments you have.

SELECT * FROM sys.fulltext_index_fragments

The rule of thumb is that if you have between 30-50 fragments per 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