Edit

sys.dm_db_fts_index_physical_stats (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Returns a row for each full-text or semantic index in each table that has an associated full-text or semantic index.

Column name Type Description
object_id int Object ID of the table that contains the index.
fulltext_index_page_count bigint Logical size of the extraction in number of index pages.
keyphrase_index_page_count bigint Logical size of the extraction in number of index pages.
similarity_index_page_count bigint Logical size of the extraction in number of index pages.

General Remarks

For more information, see Manage and Monitor Semantic Search.

Metadata

For information about the status of semantic indexing, query the following dynamic management views:

Permissions

On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Examples

The following example shows how to query for the logical size of each full-text or semantic index in every table that has an associated full-text or semantic index:

SELECT * FROM sys.dm_db_fts_index_physical_stats;  
GO

See Also

Manage and Monitor Semantic Search