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 ;

table_name

index_name

fileGroupName

used_page_count

row_count

Size_MB

partition_number

XYZ

PK_XYZ

IDX

3189667

6686016

24919 (24GB)

1

ABC

PK_ABC

IDX

1728268

9338051

13502(13 GB)

1

XYZ1

IX_ XYZ1

IDX

522498

31385087

4082

1

XYZ2

PK_ XYZ2

IDX

383227

2284271

2993

1

XYZ3

PK_XYZ3

PRIMARY

273351

292863

2135

1

XYZ4

PK_XYZ4

DATA

160181

23651237

1251

1

XYZ5

PK_XYZ5

PRIMARY

113845

31385087

889

1

XYZ6

pk_XYZ6

PRIMARY

95167

25027797

743

1

XYZ7

PK_XYZ

DATA

79412

9981030

620

1

sp_helpfile

Name

Fileid

Filename

filegroup

Size

maxsize

growth

usage

SYS

1

I:\DATA\SQL\MSSQL10. XYZ \MSSQL\Data\ XYZ_SYS.mdf

PRIMARY

54517760 KB (52GB)

Unlimited

10%

data only

LOG_1

2

I:\LOGS\SQL\MSSQL10. XYZ \MSSQL\Logs\ XYZ_ LOG_1.ldf.ldf

NULL

6826688 KB

2147483648 KB

10%

log only

DATA_1

3

I:\DATA\SQL\MSSQL10. XYZ \MSSQL\Data\ XYZ_ DATA_1.ndf

DATA

2398848 KB

Unlimited

10%

data only

HIST_1

4

I:\HIST\SQL\MSSQL10. XYZ \MSSQL\Data\ XYZ_ HIST_1.ndf

HIST

819200 KB

Unlimited

10%

data only

IDX_1

5

I:\INDEX\SQL\MSSQL10. XYZ \MSSQL\Data\ XYZ_ IDX_1.ndf

IDX

14487360 KB (14GB)

Unlimited

10%

data only

MEMO_1

6

I:\DATA\SQL\MSSQL10. XYZ \MSSQL\Data\ XYZ_ MEMO_1.ndf

MEMO

10240 KB

Unlimited

10%

data only

TMP_1

7

I:\DATA\SQL\MSSQL10. XYZ \MSSQL\Data\ XYZ_ TMP_1.ndf.ndf

TMP

10240 KB

Unlimited

10%

data only

LOOKUP_1

8

I:\DATA\SQL\MSSQL10. XYZ \MSSQL\Data\ XYZ_ LOOKUP_1.ndf

LOOKUP

1024 KB

Unlimited

1024 KB

data only

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 ;

table_name

index_name

fileGroupName

used_page_count

row_count

in_row_used_page_count

lob_used_page_count

row_overflow_used_page_count

Size_MB

XYZ

PK_XYZ

IDX

3202978

6716188

77266

3125712(24GB)

0

25023

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

Comments

  • Anonymous
    September 26, 2011
    Good Work Devashish