1

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

3
  • 1
    Since your question was around index rebuilds blowing it out, and the fact that you ran DBCC SHRINKDB, you will proably like this post from Brent Ozar and comments by Erik Darling as well as this one which references a lot of classics from Mike Walsh, Paul Randal, Gail Shaw, etc...
    – S3S
    Sep 25, 2019 at 17:51
  • thanks scsimon! It's good to have some more context for this, though I believe my mystery is more about something unintentionally decompressing during the rebuild, and what actually is living in that space, if anything, after the job completes. In Brent's cases, a SHRINKDB actually shrinks the db, and mine do nothing. Maybe suggesting something did not re-compress after rebuilding?
    – Cpt.Whale
    Sep 25, 2019 at 20:36
  • 1
    Well, I posted that for the fact that rebuilding indexes need space, and shrinking causes fragmentation. So, you can get in a loop of rebuilding, shrink, crap i'm fragmented... do it all over again, etc. It's not related to your size being returned, but more so of should you shrink at all?
    – S3S
    Sep 25, 2019 at 20:40

1 Answer 1

3

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.

1
  • 1
    Thanks! You've answered my question about the accuracy of the storage properties. I'll have to continue to investigate what is actually sitting in that extra space when this happens.
    – Cpt.Whale
    Sep 25, 2019 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.