Clustered Columnstore Indexes – part 37 (“Deleted Bitmap & Delta-Store Sizes”)

Continuation from the previous 36 parts, starting from

Since the very first CTP beta, I have been asking for some kind of a direct access to the Delta-Stores and Deleted Bitmaps, especially their respective sizes were of interest to me. The only answers from Microsoft I have got were that it was absolutely impossible for the current version.

Well, today I have found a way to consult them. An unsupported one, since some of the system views used in the queries might be changed not from Version to Version but even with a Service Pack or Cummulative Update.

If you look for information about sys.system_internals_partitions view, there is nothing particularly informative to be found in internet. The only Microsoft page that is easy to find is the one which has some basic information about type of information stored in the view.
According to the link above, it contains a combination of rowset and hobt information. There is a one-to-one correspondence between rowset and hobt.

The more precise information on this DMV you can find at the article about Table internals from brilliant Remus Rusanu.

I have spent some time looking into sys.system_internals_partitions and some other views to found a lot of very interesting informations, that there are some views, such as sys.dm_db_partition_stats that are using this DMV internally and that they have quite an interesting number of informations exposed and documented in their respective definition bodies.

Observing the columns available in the view, we can see a number of very interesting ones, such as is_columnstore which are quite new to SQL Server (available since 2012 version), and the owner_type which is principally the most important column in our case.
For the current SQL Server 2014 version the key to success is to understand the following:
– is_columnstore column identifies Segments which are composing the Columnstore Index (Clustered & Nonclustered)
– owner_type has 2 important values that should be considered:

  • 2 – identifies internal structures which are Deleted Bitmaps
  • 3 – identifies internal structures which are Delta-Stores

Note that in both cases is_columnstore column should contain value 0 (false), because both of them are not actual Segments.

For the exercise, I will use the copy of the free Contoso Retail DW database and kick off with a restore sequence:

Now we can setup a couple of Fact Tables with Clustered Columnstore Indexes:

Let us run the following query, to find out what kind of information is available for Deleted Bitmaps & Delta-Stores:

Listing Default Deleted BitmapsIn this case we are very clearly seeing the respective unique Deleted Bitmaps for each of our four fact tables with Clustered Columnstore Indexes. There are no Delta-Stores at the moment, because we have just created a our Columnstore Index.
Notice, that I did not included Segment Columnstore structures into the result of my query.

To see if we can really see a Delta-Store there, let us add another 150 rows from the table dbo.FactOnlineSales to itself:

Listing Default Deleted Bitmaps and Delta-Store This time we have a new addition (5th row) which is a Delta-Store containing 150 rows as expected. One of the visible columns on the picture is the is_unique, confirming that a Delta-Store is indeed a structure, which doest not control if the data is unique or not.

This is all very interesting, but there are absolutely no columns about what size all of those structures do have!

There is a DMV sys.allocation_units which is documented and which contains the information on how to use it.
If we are getting information about in_row_data (type=1), than the storage container associated with the allocation unit should be dealt on the following base: container_id = sys.partitions.hobt_id

Let’s run our query to join the necessary DMV’s together:

The end result in this case is empty. 🙁
I guess the documentation about undocumented DMV’s has not been completely written or updated yet. 🙂

Let’s go forward and simply connect sys.system_internals_partitions with sys.allocation_units as if the information stored in the internal_partitions are LOBs (type = 2 – Large object (LOB) data (text, ntext, image, xml, large value types, and CLR user-defined types))

Hidden_Structure_Pages_CountThis time we have success! We have all our 5 distinct elements (4 empty Deleted Bitmaps plus 1 open Delta-Store) all listed with the detailed information on which pages are being used and how.

Now we can delete a couple of thousands rows from the FactSales table and then simply go and check the final results for both Deleted Bitmaps and Delta-Stores:

Here we are with Deleted Bitmaps sizes:

And here are sizes for the Delta-Stores:
Notice that I have added having object_name(object_id) is not null; to the Delta-Stores size query, this is one of the cases I am still battling because in one of my DB I am getting a number of Delta-Stores with non-identifieable object_ids and partition_ids.
I hope to update this blog post one day, once I understand why this is happening.

to be continued with Clustered Columnstore Indexes – part 38 (“Memory Structures”)

2 thoughts on “Clustered Columnstore Indexes – part 37 (“Deleted Bitmap & Delta-Store Sizes”)

  1. tobi

    Regarding the orphaned delta stores: Try deleting all objects from that database. Restart the instance. Are there still orphaned delta stored? Then, it is a bug in SQL Server. Congratulations to your discovery.

    1. Niko Neugebauer Post author

      Hi Tobi,
      thank you for the comment, I have deleted every table in the DB but those elements still persisted in a sys.system_internals_partitions – maybe they are some kind of information that was allocated in time and waiting to be removed by some ghost cleanup.
      Note that I did rebuild every table before trying to get rid of it.

      This DMV sys.system_internals_partitions is unsupported and I feel that it is a kind of normal that some information that is not intended for the final user’s usage is available there.

      This is was a DB with a table over 20 billion rows (with just a 4 small columns) and even though the empty DB backup on the disk occupied 20 MB, an attempt to restore gives me the following message:
      The database requires 17.406.361.600 additional free bytes, while only 4.425.625.600 bytes are available.

      Funny stuff 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *