How to determine the size of a Full-Text Index on SQL Server 2008 R2?

Question

I have a SQL 2008 R2 database with some tables on it having some of those tables a Full-Text Index defined. I'd like to know how to determine the size of the index of a specific table, in order to control and predict it's growth.

Is there a way of doing this?

Wo

Maybe this could be useful: technet.microsoft.com/en-us/library/ms190370.aspx (IndexSize property) – Iow

IndexSize actually returns the size of the FTS Catalog and a catalog might have more than one index. I want the size of each index individually. – Wo

Answer 1

The catalog view sys.fulltext_index_fragments keeps track of the size of each fragment, regardless of catalog, so you can take the SUM this way. This assumes the limitation of one full-text index per table is going to remain the case. The following query will get you the size of each full-text index in the database, again regardless of catalog, but you could use the WHERE clause if you only care about a specific table.

SELECT 
   [table] = OBJECT_SCHEMA_NAME(table_id) + '.' + OBJECT_NAME(table_id), 
   size_in_KB = CONVERT(DECIMAL(12,2), SUM(data_size/1024.0))
 FROM sys.fulltext_index_fragments
 -- WHERE table_id = OBJECT_ID('dbo.specific_table_name')
 GROUP BY table_id;

Also note that if the count of fragments is high you might consider a reorganize.

Unmeet

Thanks! I'll need some time to check this as I have not been working on this project for a while... – Wo

@Wo yeah, I'm not sure what brought this question to my attention almost two years after it was asked. – Unmeet

This seems to be the umcompressed size of the full-text-fragments. By looking at sys.internal_table you can get the size of the compressed fragments and the space needed for the rest of the full-text-infrastructure. See my answer for an example query. – Ellison

Answer 2

If you are after a specific Catalogue Use SSMS - Clik on [Database] and expand the objects - Click on [Storage] - Right Click on {Specific Catalogue} - Choose Propertie and click. IN General TAB.. You will find the Catalogue Size = 'nn'

Psychopathology

Answer 3

I use something similar to this (which will also calculate the size of XML-indexes, ... if present)

SELECT  S.name,
        SO.name,
        SIT.internal_type_desc,
        rows = CASE WHEN GROUPING(SIT.internal_type_desc) = 0 THEN SUM(SP.rows)
               END,
        TotalSpaceGB = SUM(SAU.total_pages) * 8 / 1048576.0,
        UsedSpaceGB = SUM(SAU.used_pages) * 8 / 1048576.0,
        UnusedSpaceGB = SUM(SAU.total_pages - SAU.used_pages) * 8 / 1048576.0,
        TotalSpaceKB = SUM(SAU.total_pages) * 8,
        UsedSpaceKB = SUM(SAU.used_pages) * 8,
        UnusedSpaceKB = SUM(SAU.total_pages - SAU.used_pages) * 8
FROM    sys.objects SO
INNER JOIN sys.schemas S ON S.schema_id = SO.schema_id
INNER JOIN sys.internal_tables SIT ON SIT.parent_object_id = SO.object_id
INNER JOIN sys.partitions SP ON SP.object_id = SIT.object_id
INNER JOIN sys.allocation_units SAU ON (SAU.type IN (1, 3)
                                        AND SAU.container_id = SP.hobt_id)
                                       OR (SAU.type = 2
                                           AND SAU.container_id = SP.partition_id)
WHERE   S.name = 'schema'
        --AND SO.name IN ('TableName')
GROUP BY GROUPING SETS(
                       (S.name,
                        SO.name,
                        SIT.internal_type_desc),
                       (S.name, SO.name), (S.name), ())
ORDER BY S.name,
        SO.name,
        SIT.internal_type_desc;

This will generally give numbers higher than sys.fulltext_index_fragments, but when combined with the sys.partitions of the table, it will add up to the numbers returned from EXEC sys.sp_spaceused @objname = N'schema.TableName';.

Tested with SQL Server 2016, but documentation says it should be present since 2008.

Ellison