Blog Post

Clustered Columnstore Indexes – part 38 (“Memory Structures”)

,

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

In the past I have been writing quite a lot on the matter of the Columnstore structures and their performance, and so it’s definitely time to make some explanations of what is going on within a SQL Server memory once we are using Clustered Columnstore Indexes.

First of all, it is important to understand that ever since SQL Server 2012 that had introduced Nonclustered Columnstore Indexes, there have been massive changes to the internal memory structures, and once working with Columsntore we are not using Buffer Pool exclusively.

Columnstore Object Pool

Since SQL Server 2012, we have a special space in memory (Cache Store) reserved for the Columnstore Indexes (Clustered & Nonclustered) and it’s name is Columnstore Object Pool. This Cache Store is located outside of the Buffer Pool and it receives new 8k Memory Pages from any-page allocator, such as Buffer Pool itself. The only interesting parts of the Columnstore Indexes for Columnstore Object Pool are the compressed segments, and all other structures such as Delta-Stores and Deleted Bitmaps to my understanding are located in the good old Buffer Pool.

Columnstore Object Pool uses Large Pages stored sequentially for allocation. In practice Large Pages allocation allows to allocate space in chunks from 2MB to 16MB instead of just 4KB.

Internals

I understand that at the moment when we are reading Columnstore data from the disk, it is being read directly into Columnstore Object Pool without decompression, and then all the respective pages from the Segments are decompressed into Buffer Pool for returning the result. And so we are basically dealing with 2 copies of Columnstore data at the same time – one is compressed in the Columnstore Object Pool and the other one is decompressed version of it in the Buffer Pool.

If we are trying to read the Segments which are already contained in Columnstore Object Pool, then we should simply read the decompressed information from the Buffer Pool (should the respective page being purged from Buffer Pool it is immediately read back into Buffer Pool), but in the case when we are accessing new Segments which are not contained in Columnstore Object Pool yet, then we are getting them from the disk and decompressing into Buffer Pool before returning information.

It is important to understand that Columnstore Object Pool and Buffer Pool essentially are looking to get memory pages from the same source, and that they are “fighting” for the same resources which means that in the cases when we do not have enough memory, we shall be dealing with memory pressure issues.

The amount of memory allocated to the Columnstore Object Pool is tracked by the new Memory Clerk – CACHESTORE_COLUMNSTOREOBJECTPOOL.

Also, we have a brand new DMV since SQL Server 2012 – sys.dm_os_memory_broker_clerks, undocumented as you would expected. ?? This DMV delivers information about principal memory consumers in our SQL Server instance.

Since Columnstore Indexes are not NUMA aware and I do not expect this to change soon, because I do not believe that the effort put into it will meet the bar of the price/improvement at Microsoft. Which is a pity.

Showcase

I shall be using Contoso Retail DW database for the showcase.

Let’s kick off with a usual procedure of restoring the database, dropping all Primary & Foreign Keys and creating 4 Clustered Columnstore Indexes on our principal Fact Tables:

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;
GO
use ContosoRetailDW;
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_FactSales
on dbo.FactSales;

I will kick off with cleaning up everything by removing all clean buffers:

DBCC DropCleanBuffers;

Now let us verify the health status of our Cache in SQL Server instance, by issuing the following query to dm_os_memory_cache_counters DMV and specifying Columnstore Object Pool:

select name, type, pages_kb, pages_in_use_kb, entries_count, entries_in_use_count
from sys.dm_os_memory_cache_counters 
where type = 'CACHESTORE_COLUMNSTOREOBJECTPOOL';

Health Cache StatusNotice that this DMV has suffered some major changes in 2012 version of SQL Server – there is no more separation into Single & Multiple Changes – this has been all very well documented at the dm_os_memory_cache_counters page.

I have no idea why it has been always 32KB occupied by the Columnstore Object Pool by default, but I was unable to get rid of it – maybe this is some basic art of pre-fetching and initialising it, I hope that with the time this can be clarified.

Let us take a look into the Memory Clerks of SQL Server tracking Columnstore Object Pool allocations.

select name, type, memory_node_id, pages_kb, page_size_in_bytes, virtual_memory_reserved_kb, virtual_memory_committed_kb
,shared_memory_reserved_kb, shared_memory_committed_kb
from sys.dm_os_memory_clerks 
where type = 'CACHESTORE_COLUMNSTOREOBJECTPOOL';

Memory ClerksWe can see the very same 32KB pre-allocated from the 8KB pages, but with no real space has been given out yet.

For all I understand we have issued a couple of queries even though reading system informations, and so we should have some memory allocated to Buffer Pool while no space being allocated to the Columnstore Object Pool:

select *
from sys.dm_os_memory_broker_clerks;

Memory Broker ClerksAs expected we have just one entry at the moment with ~18 MB allocated to Buffer Pool and no active Memory being allocated to Columnstore Object Pool.

From now I will be checking on my segments of FactSales table in order to construct a query that has a predictable and calculate-able size of the Segments that my future query shall be reading – in this case I am checking on the SalesKey column and its Segments.

select column_id, segment_id, row_count, base_id, min_data_id, max_data_id, on_disk_size
from sys.column_store_segments seg
inner join sys.partitions part
on seg.partition_id = part.partition_id
where column_id = 1 and 
part.object_id = object_id('FactSales');

SalesKey Segments from FactSalesThese result mean that in order to get a good segment elimination we can read SalesKey from 1 to 11628, which in my current situation shall guarantee that only the very first Segment with ID = 0.

Before advancing with a query that involves SalesAmount, let’s check on the size of the first Segment (ID = 0) of the FactSales table, where the id of the column is equal to 16:

select column_id, segment_id, row_count, base_id, min_data_id, max_data_id, on_disk_size
from sys.column_store_segments seg
inner join sys.partitions part
on seg.partition_id = part.partition_id
where column_id = 16 and 
part.object_id = object_id('FactSales');

SalesAmount Segments from FactSalesHere we are with the exact sizes for each of the SalesAmount Segments.

Now let’s run the query, cleaning out buffers in advance:

DBCC DropCleanBuffers;
select Sum([SalesAmount]) 
from FactSales
where SalesKey < 11628;

Mathematics for Columnstore Allocations:

In total we are dealing with around 4 MB of compressed Segments, because of the following formula (1603400 + 2796792)/1024 = 4297.06 KB, but will the amount of information be exactly what we expect:

select name, type, pages_kb, pages_in_use_kb, entries_count, entries_in_use_count
from sys.dm_os_memory_cache_counters 
where type = 'CACHESTORE_COLUMNSTOREOBJECTPOOL';
select name, type, memory_node_id, pages_kb, page_size_in_bytes, virtual_memory_reserved_kb, virtual_memory_committed_kb
,shared_memory_reserved_kb, shared_memory_committed_kb
from sys.dm_os_memory_clerks 
where type = 'CACHESTORE_COLUMNSTOREOBJECTPOOL';
select *
from sys.dm_os_memory_broker_clerks;

Memory_Structures_After_Issued_QueryWhat we actually see here is that we have 26 entries in Columnstore Object Pool which are occupying 4840 KB in total, value which is a little bit superior to what I have calculated earlier.

What is going on ?

I decided to try to take a look into DMV sys.dm_os_buffer_descriptors to see if there is anything that I can understand and relate to:

select object_name(object_id) as TableName, page_type, row_count, type_desc
    from sys.allocation_units as alloc
        inner join sys.partitions as part
on alloc.container_id = part.partition_id 
inner join sys.dm_os_buffer_descriptors buff
on buff.allocation_unit_id = alloc.allocation_unit_id
where alloc.type = 2 and object_id = object_id('FactSales')
order by buff.page_type;

Buffer Descriptors in DetailsThere are 13 pages currently cached and TEXT_TREE_PAGE as well as TEXT_MIX_PAGE both correspond to the LOB data used to store Columnstore Indexes, while IAM_PAGE is an Index Allocation Map which keeps track of the Extents in each of the 4 GB space. IAM tracking includes Columnstore Indexes, which are stored as LOBs.

Very interesting information, which brings more understanding into memory allocations, but it gives me more confusion at the moment than the actual answers.

Current Solution

We can consult the contents of the sys.dm_os_memory_cache_entries DMV, which returns information & statistics about all entries in caches in our instance for Columnstore Object Pool, maybe it can put some light into the matter of “missing” memory space:

select name, entry_data, pages_kb
from sys.dm_os_memory_cache_entries 
where type = 'CACHESTORE_COLUMNSTOREOBJECTPOOL';

Cache Entries for Columnstore Object PoolThis is where I am going to speculate quite a bit, and so here we go:

object_type = 1 -> this is our actual Segment

object_type = 2 -> this is a Global Dictionary

object_type = 4 -> this is a Local Dictionary

object_type = 6 -> this item is appearing only when we have no dictionaries assigned to our Segment (both local and global dictionaries references are pointing to -1), and so I call it a “Future Dictionary” ?? It can be easily recognised since it’s column_id is always set to -1.

And so we actually see that while reading 1 Row Group from 2 different Columns we have 1 “Future” Dictionary for Column 1 (SalesKey) and 2 Dictionaries (Global & Local) for the Column 16 (SalesAmount).

We can confirm this findings by issuing the following query to the sys.column_store_segments DMV:

select segment_id, column_id, primary_dictionary_id, secondary_dictionary_id
from sys.column_store_segments seg
join sys.partitions part
on seg.hobt_id = part.hobt_id
where object_id= object_id('FactSales')
and column_id in (1,16) and segment_id = 0;

Assigned Dictionaries to SalesKey & SalesAmount columnsNow that we have this information, we can go further and consult the sizes of those 2 dictionaries with ids 0 & 1, and so I issue the next query to find out this information from sys.column_store_dictionaries DMV:

select object_name(object_id) as TableName, column_id, dictionary_id, on_disk_size
from sys.column_store_dictionaries dict
join sys.partitions part
on dict.hobt_id = part.hobt_id
where object_id= object_id('FactSales')
and column_id in (1,16);

Used Dictionaries SizesI can see 385000 & 68592 bytes respectively occupied by those dictionaries, and since they occupy space in Columnstore Object Pool we should definitely add them to our equation.

Final Result: (1603400 (Segment) + 2796792 (Segment) + 385000(Global Dictionary) + 68592 (Local Dictionary) )/1024 = 4740.02 KB

This means that we are still missing around 100KB of space, and I hope that some answers will arrive in the future.

I know that the “Future Dictionary” should occupy some space, but I have no information on how much, I have checked to verify if Deleted Bitmaps are already occupying some space, but this does not seems to be the case.

At the moment I am inclined to believe that this space is separated between the “Future Dictionary” and the rest should be some kind of pre-reserved memory space from the Large Pages, such as those 32KB that are reserved by the default as well as some free pre-allocated space, ready to be used.

to be continued …

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating