Continuation from the previous 36 parts, starting from http://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/

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:

USE [master]
alter database ContosoRetailDW
set SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [ContosoRetailDW] 
FROM  DISK = N'C:\Install\ContosoRetailDW.bak' WITH  FILE = 1,  
MOVE N'ContosoRetailDW2.0' TO N'C:\Data\ContosoRetailDW.mdf', 
MOVE N'ContosoRetailDW2.0_log' TO N'C:\Data\ContosoRetailDW.ldf',  
NOUNLOAD,  STATS = 5;
alter database ContosoRetailDW
set MULTI_USER;

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

alter table dbo.[FactOnlineSales] DROP CONSTRAINT PK_FactOnlineSales_SalesKey
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT PK_FactStrategyPlan_StrategyPlanKey
alter table dbo.[FactSales] DROP CONSTRAINT PK_FactSales_SalesKey
alter table dbo.[FactInventory] DROP CONSTRAINT PK_FactInventory_InventoryKey
alter table dbo.[FactSalesQuota] DROP CONSTRAINT PK_FactSalesQuota_SalesQuotaKey
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimCurrency
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimCustomer
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimDate
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimProduct
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimPromotion
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimStore
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimAccount
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimCurrency
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimDate
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimEntity
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimProductCategory
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimScenario
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimChannel
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimCurrency
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimDate
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimProduct
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimPromotion
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimStore
alter table dbo.[FactInventory] DROP CONSTRAINT FK_FactInventory_DimCurrency
alter table dbo.[FactInventory] DROP CONSTRAINT FK_FactInventory_DimDate
alter table dbo.[FactInventory] DROP CONSTRAINT FK_FactInventory_DimProduct
alter table dbo.[FactInventory] DROP CONSTRAINT FK_FactInventory_DimStore
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimChannel
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimCurrency
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimDate
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimProduct
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimScenario
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimStore;
GO
Create Clustered Columnstore Index PK_FactOnlineSales
    on dbo.FactOnlineSales;
Create Clustered Columnstore index PK_FactSales
on dbo.FactSales;
Create Clustered Columnstore index PK_FactInventory
on dbo.FactInventory;
Create Clustered Columnstore index PK_FactSalesQuota
on dbo.FactSalesQuota;

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

select object_name(object_id),
*
from  sys.system_internals_partitions p
where p.is_columnstore = 0
 and ownertype in (2,3)

In 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:

set identity_insert dbo.FactOnlineSales ON
insert into dbo.FactOnlineSales
(OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate)
select top 150 *
from dbo.FactOnlineSales;
set identity_insert dbo.FactOnlineSales OFF

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:

select object_name(ip.object_id),
*
from  sys.system_internals_partitions ip
inner join sys.partitions p
on ip.partition_id = p.partition_id
inner join sys.allocation_units al
on al.container_id = p.hobt_id
where ip.is_columnstore = 0
 and ownertype in (2,3);

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))

select object_name(ip.object_id),
al.*
from  sys.system_internals_partitions ip
inner join sys.allocation_units al
on al.container_id = ip.partition_id
where ip.is_columnstore = 0
 and ownertype in (2,3)

This 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:

Delete top (51000) 
from dbo.FactSales;

select object_name(ip.object_id) as TableName,
sum(al.used_pages) * 8 as 'size (KB)'
from  sys.system_internals_partitions ip
inner join sys.allocation_units al
on al.container_id = ip.partition_id
where ip.is_columnstore = 0
and ownertype = 2
    group by object_name(object_id);

Here we are with Deleted Bitmaps sizes:

select object_name(ip.object_id) as TableName,
sum(al.used_pages) * 8 as 'size (KB)'
from  sys.system_internals_partitions ip
inner join sys.allocation_units al
on al.container_id = ip.partition_id
where ip.is_columnstore = 0
and ownertype = 3
    group by object_name(object_id)
having object_name(object_id) is not null;

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”)