sys.fulltext_index_fragments (Transact-SQL)


Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

A fulltext index uses internal tables called full-text index fragments to store the inverted index data. This view can be used to query the metadata about these fragments. This view contains a row for each full-text index fragment in every table that contains a full-text index.

Column nameData typeDescription
table_idintObject ID of the table that contains the full-text index fragment.
fragment_object_idintObject ID of the internal table associated with the fragment.
fragment_idintLogical ID of the full-text index fragment. This is unique across all fragments for this table.
timestamptimestampTimestamp associated with the fragment creation. The timestamps of more recent fragments are larger than the timestamps of older fragments.
data_sizeintLogical size of the fragment in bytes.
row_countintNumber of individual rows in the fragment.
statusintStatus of the fragment, one of:

0 = Newly created and not yet used

1 = Being used for insert during fulltext index population or merge

4 = Closed. Ready for query

6 = Being used for merge input and ready for query

8 = Marked for deletion. Will not be used for query and merge source.

A status of 4 or 6 means that the fragment is part of the logical full-text index and can be queried; that is, it is a queryable fragment.

The sys.fulltext_index_fragments catalog view can be used to query the number of fragments comprising a full-text index. If you are experiencing slow full-text query performance, you can use sys.fulltext_index_fragments to query for the number of queryable fragments (status = 4 or 6) in the full-text index, as follows:

SELECT table_id, status FROM sys.fulltext_index_fragments  
   WHERE status=4 OR status=6;  

If many queryable fragments exist, Microsoft recommends that you reorganize the full-text catalog that contains the full-text index to merge the fragments together. To reorganize a of full-text catalog use ALTER FULLTEXT CATALOGcatalog_name REORGANIZE. For example, to reorganize a full-text catalog named ftCatalog in the AdventureWorks2012 database, enter:

USE AdventureWorks2012;  

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission.

Object Catalog Views (Transact-SQL)
Populate Full-Text Indexes

Community Additions