The mystery of incorrect filegroup size

Recently, there was a question that why the SQL Server DMVs were reflecting incorrect information with respect to the size of the indexes, files and filegroups. The following data supported the question:

SELECT TOP 50 OBJECT_NAME(p.object_id) AS table_name,

        i.[name] AS index_name,

        f.name AS fileGroupName,

        p.used_page_count,

        p.row_count,

        p.used_page_count * 8192 / ( 1024 * 1024 ) AS Size_MB,

        p.partition_number

FROM sys.dm_db_partition_stats p

JOIN sys.indexes AS i ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

JOIN sys.filegroups f ON i.data_space_id = f.data_space_id

ORDER BY p.used_page_count DESC ;

sp_helpfile

On looking at the data on first impression it seems that there is a discrepancy in the index size and the corresponding filegroup size. Cumulative sizes of 2 largest indexes in the database which are in the filegroup IDX is approx. 40 GB. But the size of the filegroup itself is approx. 14GB. Moreover this filegroup has ONLY 1 file and it was showing as 15GB when checked in Windows explorer! How is that possible?

First it was important to verify that the queries used in the above information were accurate. Looked through BOL and verified that the correct DMVs were used in the queries.

Next, ran DBCC UPDATEUSAGE <database_name> to confirm that the information in the DMV’s was updated. No avail, there was no change in the above data.

We then looked at the table definition :-

CREATE TABLE [dbo].[XYZ](

                [Col1] [int] IDENTITY(1,1) NOT NULL,

                [Col2] [int] NOT NULL,

                [Col3] [text] NOT NULL,

                [Col4] [dbo].[UDT_1] NOT NULL,

                [Col5] [dbo].[UDT_2] NOT NULL,

                [Col6] [dbo].[UDT_3] NOT NULL,

                [Col7] [dbo].[UDT_2] NOT NULL,

CONSTRAINT [PK_Const1] PRIMARY KEY CLUSTERED

(

                [Col2] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [IDX],

CONSTRAINT [PK_Const2] UNIQUE NONCLUSTERED

(

                [Col3] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [IDX] TEXTIMAGE_ON [PRIMARY]

We see here the while creating the table, the TEXTIMAGE_ON clause is used.

As per Books Online:

TEXTIMAGE_ON:

Are keywords indicating that the text, ntext, and image columns are stored on the specified filegroup. TEXTIMAGE ON is not allowed if there are no text, ntext, or imagecolumns in the table. If TEXTIMAGE_ON is not specified, the text, ntext, and image columns are stored in the same filegroup as the table

This indicated that the customer was using TEXTIMAGE_ON clause due to which all the LOB data was being stored in a different filegroup i.e PRIMARY in this case due to which there was size discrepancy. To confirm that the LOB datatypes were consuming all that space we ran the below query (which is basically the same query the Customer ran earlier but with some additional columns)

SELECT TOP 1 OBJECT_NAME(p.object_id) AS table_name,

        i.[name] AS index_name,

        f.name AS fileGroupName,

        p.used_page_count,

        p.row_count, p.in_row_used_page_count , p.lob_used_page_count , p.row_overflow_used_page_count ,

        p.used_page_count * 8192 / ( 1024 * 1024 ) AS Size_MB,

        p.partition_number

FROM sys.dm_db_partition_stats p

JOIN sys.indexes AS i ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

JOIN sys.filegroups f ON i.data_space_id = f.data_space_id

ORDER BY p.used_page_count DESC ;

This showed that lob_used_page_count was around 3125712 pages i.e around 24GB which was part of PRIMARY filegroup and accounted for the mysterious place.

Mystery resolved!!

Written By :- Devashish Salgaonkar

Support Engineer, Microsoft SQL server Support

Reviewed By: – Akbar Farishta

Technical Lead, Microsoft SQL server Support