Space Calculation for SQL Server Table: What's wrong with my query?

Question

I'm using the following query in order to calculate some space related measurements on a particular table in my SQL Server database:

SELECT 
 t.name AS TableName, 
 p.rows,
 (sum(a.total_pages) * 8) as reserved,
 (sum(a.data_pages) * 8) as data,
 N'Not Needed' as index_size,
 (sum(a.total_pages) * 8) - (sum(a.used_pages) * 8) as unused
FROM 
 sys.tables t
INNER JOIN 
 sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = N'XYZ' 
GROUP BY t.name, p.rows
ORDER BY 
 1 Desc;

It gives me correct results for calculating number of rows, reserved space, and unused space. However, when I compare its output with the output from running sp_spaceused stored procedure, I observe a different value for space used by data:

sp_usedspace vs my query

How can I fix it?

• NikitaSerbskiy: you can open sys.sp_spaceused procedure in SSMS and see that number of data pages is the sum of in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count from sys.dm_db_partition_stats view
• Aaron Bertrand: Related: dba.stackexchange.com/q/202450/1186

Answer 1

The documentation for sys.allocation_units is incorrect, it seems. It states that data_pages is number of used pages and it should include LOB pages.

LOB pages are pages for data types such as the (MAX) types, XML, geospatial and columnstore data.

But, looking on my machine (SQL server 2019), I see 0 data pages for LOB data. If you remove the GROUP BY and look at the individual rows, you'll see what I mean. So, you would have to CASE whether it is LOB data and use some other column in that case.

I've used the FactResellerSalesXL_CCI in the AdventureworksDW2016 database for my test. Adjust to your liking. I parameterized the schema name and table. See the discrepancy between the resulting column names data (your original calculation) and data_corrected (my adjusted calculation):

DECLARE @s sysname, @t sysname, @f nvarchar(256)
SET @s = 'dbo'
SET @t = 'FactResellerSalesXL_CCI'
SET @f = QUOTENAME(@s) + '.' + QUOTENAME(@t)
SELECT 
 t.name AS TableName, 
 a.type_desc,
 i.index_id,
 p.rows,
 a.total_pages * 8 as reserved,
 a.data_pages * 8 as data,
 (CASE WHEN a.type_desc = 'LOB_DATA' THEN a.used_pages ELSE a.data_pages END) * 8 as data_corrected,
 N'Not Needed' as index_size,
 a.total_pages * 8 - a.used_pages * 8 as unused
FROM 
 sys.tables t
INNER JOIN 
 sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = @t
ORDER BY 
 1 Desc;

I leave it to you to bring back the aggregation. :-)