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:

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, showing the inconsistensies in 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