Sql-server – Table properties in SSMS and sp_spaceused don’t match

sql serversql-server-2017

I have a table Table_A where when I right-click the table in SSMS and go to Properties > Storage, it shows the following:

Data space: 5 GB              
Index space: 250 MB
Number of partitions: 400
Partitions using default columnstore compression: 1-400

if I do sp_spaceused Table_A, it shows entirely different numbers (rounded):

Name    rows    reserved        data            index_size  unused
Table_A 5000000 100000000 KB    100000000 KB    25000 KB    28000 KB

What could be causing the difference in space consumed?

The database's properties page is showing that 100 GB reserved space as "Available", but fails to reclaim any of it during a dbcc shrinkdb

This table "grew" suddenly while rebuilding a clustered columnstore index where the index size grew to ~20 GB, but shrank back down after the job completed. I'm still trying to wrap my head around columnstore compression and the indexes, so I feel like I'm missing something simple

Best Answer

Data space used

The query that gets used to calculate the data space on the table via ssms when selecting the storage properties of my dbo.Employees table

exec sp_executesql N'
        declare @PageSize float
        select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1



SELECT

        case
        when (tbl.is_memory_optimized=0) then
        ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
        FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        where i.object_id = tbl.object_id),0.0)
        else
        isnull((select (tms.[memory_used_by_table_kb])
        from [sys].[dm_db_xtp_table_memory_stats] tms
        where tms.object_id = tbl.object_id), 0.0)
        end
       AS [DataSpaceUsed]
FROM
sys.tables AS tbl
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
OPTION (FORCE ORDER)
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'1',@_msparam_1=N'E',@_msparam_2=N'Employees',@_msparam_3=N'dbo'

Executing this query gives me:

DataSpaceUsed
20008

This is the data in KB's, for the calculation, keep reading.

Via SSMS it gives me:

enter image description here

Lets explore a part of this query further, for normal, non memory optimized tables.

when (tbl.is_memory_optimized=0) then
        ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

With the pagesize variable as a value of dbo.spt_values / 1024

select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1

Which can be changed to

    declare @PageSize float
    select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
  select @PageSize

= 8

Which is the 8KB page size as used by sql server

Then the next part, CASE WHEN a.type <> 1 THEN a.used_pages

With the type 1 being:

1 = In-row data (all data types, except LOB data types)

Source

And the next part

WHEN p.index_id < 2 THEN a.data_pages ELSE 0

To get the clustered / heap data pages, without the nonclustered index pages.

So in short, get the lob data & data pages belonging to the heap or clustered index that is in sys.allocation_units

Where does this take us?

This gets us to link to a different Q/A, with the asker trying to get a performant and correct space used query by using the DMV's.

Same goes for the other linked answer, both excellent answers by by Solomon Rutzky

The following base query works. It gets the same output as sp_spaceused, using the same algorithm, but much more efficiently...

So yeah, sp_spaceused will be more correct as the algorithm used to calculate the size accounts for more information, the query in the linked Q/A does the same but more efficiently.

You can take a look at the dmv's touched by running sp_spaceused with the actual execution plan enabled with CTRL+P, or look at this one, when I ran sp_spaceused on dbo.Employees

The usage of sys.dm_db_partition_stats in sp_spaceused and the query linked vs. the usage of sys.allocation_units via ssms is also apparent.

Related Question