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