Fulltext Fragmentation (iFTS)

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

 

Tagged: fulltext index, fulltext index fragmentation, iFTS, Katmai, SQL Server 2008, sql2008

Posted in: Article, Full Text Search, iFTS, Katmai, SQL