Few weeks ago, I had the opportunity to give a session about the clustered columnstore index feature (CCI) at our In-Memory event dedicated to In-Memory technologies for Microsoft SQL Server, Oracle and SAP HANA. During our session, I explained the improvement made by Microsoft on SQL Server 2014 with the introduction of new clustered columnstore index (CCI).

The CCI includes a new structure that allows update operations: the delta store. Indeed, insert operations go directly in the delta store. Delete operations are logical and go in the deleted bitmap in the delta store and finally update operations are split into two basic operations DELETE operation followed by INSERT operation. I was very interested in how SQL Server deals with both the structures (delta store and columnstore) and memory in different scenarios. This blog post is a result of my studies and will probably concern those who are interested by internal stuff. In fact, I discussed with one of my (oracle) friend and he asked me some interesting questions about CCI and the memory management topic.

First of all, let’s begin with the storage concept: the delta store consists of a traditional row-based storage unlike the columnstore index which is column-oriented storage. The two structures are managed differently by SQL Server and they have their own memory space – respectively the CACHESTORE_COLUMSTOREOBJECTPOOL for the columnstore structure and the traditional buffer pool (BP) for the row store structure. When columnstore data are fetched from disk to memory, they come first to the BP and then to the columnstore memory pool. We may get information about columnstore memory pool by using the following query:

 

select
       type,
       name,
       memory_node_id,
       pages_kb,
       page_size_in_bytes
from sys.dm_os_memory_clerks
where type = ‘CACHESTORE_COLUMNSTOREOBJECTPOOL’;
go

 

blog_55_-_2-_CCI_memory_management_memory_clerk-_

 

First scenario

We’ll see how SQL Server behaves by reading data exclusively from the delta store. Let’s begin with a pretty simple table:

 

— create table test_CCI
if object_id(‘test_CCI’, ‘U’) is not null
       drop table test_CCI;
go
 
create table test_CCI
(
       id int not null identity(1,1),
       col1 char(10) not null default ‘col_’
);
go

 

Next, let’s create a CCI that will include 1 compressed row group and 1 delta store (open state):

 

set nocount on;
 
— insert 1000 rows
insert test_CCI default values;
go 1000
 
— create CCI
create clustered columnstore index [PK__test_CCI__3213E83F3A6FE3AC]
on test_CCI
go
 
— insert 1 rows in order to create a delta store (OPEN state)
insert test_CCI default values;
go 1

 

Let’s have a look at the CCI row group’s information:

 

select
       object_name(object_id) as table_name,
       index_id,
       row_group_id,
       delta_store_hobt_id,
       state_description as [state],
       total_rows,
       deleted_rows,
       size_in_bytes
from sys.column_store_row_groups
where object_id = object_id(‘test_CCI’);
go

 

blog_55_-_2-_CCI_configuration-_

 

Let’s execute the first query that will fetch data from the record to the delta store

 

dbcc dropcleanbuffers;
 
select
       max(id)
from dbo.test_CCI
where id = 1001

 

Let’s have a look at the memory cache entries related to the CCI memory pool:

 

select
       name,
       in_use_count,
       is_dirty,
       pages_kb,
       entry_data,
       pool_id
from sys.dm_os_memory_cache_entries
where type = ‘CACHESTORE_COLUMNSTOREOBJECTPOOL’;
go

 

blog_55_-_3-_CCI_memory_management_cache_entries_empty-_

No entries and this is what I expected because data come only from delta store and the buffer pool is the only one concerned by this scenario. Another important point: segments are eliminated directly from disk. In order to prove it, I created an extended event to get segment elimination information as follows:

 

CREATE EVENT SESSION [cci_segment_elimination]
ON SERVER
ADD EVENT sqlserver.column_store_segment_eliminate
(
   WHERE ([sqlserver].[database_name]=N’db_test’)
)
ADD TARGET package0.event_file
(
       SET filename= N’C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLLogcci_segment_elimination’
)
WITH
(
       MAX_MEMORY=4096 KB,
       EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
       MAX_DISPATCH_LATENCY=30 SECONDS,
       MAX_EVENT_SIZE=0 KB,
       MEMORY_PARTITION_MODE=NONE,
       TRACK_CAUSALITY=OFF,
       STARTUP_STATE=OFF
)
GO

 

And after looking at the extended event file, I noticed that the segment was eliminated by SQL Server as expected.

 

blog_55_-_4-_CCI_segment_elimination

 

The hobt_id value relies to the compressed segment in the columnstore index:

 

select
       partition_id,
       hobt_id,
       column_id,
       segment_id,
       row_count,
       min_data_id,
       max_data_id
from sys.column_store_segments
where hobt_id = 72057594041925632

 

blog_55_-_5-_CCI_segment

 

Second scenario

It will consist in reading data directly from the columnstore segment.

 

dbcc dropcleanbuffers
go
 
select
       max(id)
from dbo.test_CCI
where id = 1

 

With the previous script that uses the sys.dm_os_memory_cache_entries DMV we can see this time, two cached entries from the columnstore object pool:

 

blog_55_-_1-_CCI_memory_management_-_cache_entries

 

 

I would like to thank Sunil Argarwal (Principal Program Manager in SQL Server Storage Engine) for his kindness and some information he gave me, in order to read the above entry data column values. [Object type] is a very useful information here with the following meaning:

0x0 = Un-initalized object
0x1 = Column segment
0x2 = Primary dictionary for a column
0x4 = Secondary dictionary for a column segment
0x5 = the primary dictionary with reverse HT initialized, used for bulk insert
0x6 = Delete bitmap – used temporarily when reading from disk

So let’s correlate with the retrieved entry data column values. SQL Server fetched the concerned segment (object_id = 1) from disk to the columnstore object memory. However, let’s have a look at the column_id value (= 2 here). In fact, I expected to get value =1 which might be related to the id column in the table dbo.test_CCI. I performed some other tests and they let me think that the column_id from the entry_data column value is in fact equal to the column_id minus 1 from the concerned table but I will check this point in the near future.

Moreover, according to the Sunil’s information, the deleted bitmap (object_id=6) was also fetched by SQL Server. I can imagine that SQL Server needs to read it to retrieve deleted records. My feeling is that these operations are fully logical and SQL Server has no way to identify a deleted record from a segment without reading the deleted bitmap.

We can assume that the columnstore memory pool is a room for storing the columnstore segments and segments are stored in LOB pages. Does SQL Server read data directly from the columnstore memory pool?

Let’s go back to the previous test. As a reminder, we want to get the max (id) from the columnstore segment related to the id columm. So SQL Server needs to read the related segment in this case. Let’s see if we can retrieve a corresponding page in the buffer pool by using the following script:

select
       page_type,
       count(*) as nb_pages
from sys.dm_os_buffer_descriptors
where database_id = db_id(‘db_test’)
group by page_type
order by page_type

 

blog_55_-_7-_CCI_buffer_pool

 

Ok, there are plenty of pages in the buffer pool related to the db_test database. Let’s focus first on data page by using the following script that consists in retrieving data pages only for the dbo.test_CCI table:

 

if object_id(‘tempdb..#buffer_descriptor_pages’) is not null
       drop table #buffer_descriptor_pages;
go
 
create table #buffer_descriptor_pages
(
       num INT null,
       ParentObject varchar(100) not null,
       [Object] varchar(100) not null,
       Field varchar(100) not null,
       VALUE varchar(100) not null
);
 
 
 
declare @sql varchar(max) = ;
declare @database_id int;
declare @file_id int;
declare @page_id int;
declare @i int = 0;
 
declare c_pages cursor fast_forward
for
select
       database_id, file_id, page_id
from sys.dm_os_buffer_descriptors
where database_id = db_id(‘db_test’)
       and page_type = ‘DATA_PAGE’;
 
open c_pages;
 
fetch next from c_pages into @database_id, @file_id, @page_id;
 
while @@fetch_status = 0
begin
 
       set @sql = ‘dbcc traceon (3604); dbcc page(‘ + cast(@database_id as varchar(10))
                                                + ‘, ‘ + cast(@file_id as varchar(10))
                                                                          + ‘, ‘ + cast(@page_id as varchar(10))
                                                                          + ‘, 3) with tableresults’;
 
       insert into #buffer_descriptor_pages(ParentObject, [Object], Field, VALUE)
       exec(@sql);
      
       update #buffer_descriptor_pages
       set num = @i
       where num is null;
 
       set @i = @i + 1;
       fetch next from c_pages into @database_id, @file_id, @page_id;    
end
      
close c_pages;
deallocate c_pages;
 
select *
from #buffer_descriptor_pages
where num in(select num
                           from #buffer_descriptor_pages
                           where Field = ‘Metadata: ObjectId’
                                  and VALUE = object_id(‘dbo.test_CCI’));

 

In my case, I retrieved only one page with the following detail:

 

blog_55_-_7-_CCI_page_compressed_details

 

We get a compressed data page and, to be more precise, a data page that comes from the delta store (id = 1001). Remember that the segment elimination is not performed for the delta store. This is why I got this page in my case.

Next, let’s have a look at the LOB pages (our segments)

 

select
       database_id,
       file_id,
       page_id,
       allocation_unit_id,
       row_count
from sys.dm_os_buffer_descriptors
where database_id = db_id(‘db_test’)
       and page_type = ‘TEXT_MIX_PAGE’
             and allocation_unit_id IN(select au.allocation_unit_id
                                        from sys.allocation_units as au
                                                         join sys.partitions as p
                                                            on p.hobt_id = au.container_id
                                                         where p.object_id = object_id(‘dbo.test_CCI’));

 

blog_55_-_8-_CCI_LOB_pages

 

We have one LOB page (TEXT_MIXPAGE type) but it seems to be empty and I admit that I don’t have any idea of this page. I will update this blog post later if I get a response.

So, to summarize and according to my tests, it’s seems that SQL Server reads LOB pages directly from the columnstore object pool and doesn’t need to use the BP in this case.

 

Third scenario

It will consist in updating data from the columnstore index and understanding how SQL Server behaves in this case.

 

alter index [PK__test_CCI__3213E83F3A6FE3AC]
on [dbo].[test_CCI] rebuild

select
       object_name(object_id) as table_name,
       index_id,
       row_group_id,
       delta_store_hobt_id,
       state_description as [state],
       total_rows,
       deleted_rows,
       size_in_bytes
from sys.column_store_row_groups
where object_id = object_id(‘test_CCI’);

 

blog_55_-_9-_CCI_without_deltastore

 

Next, let’s update the columnstore index by using the following query:

checkpoint;
go
 
dbcc dropcleanbuffers;
go
 
update dbo.test_CCI
set col1 = ‘toto’

 

At this point, a delta store page is created by SQL Server and we have to think differently because the storage has changed from columnar to row store. So let’s have a look at the modified pages related to the columnstore index.

 

if object_id(‘tempdb..#buffer_descriptor_pages’) is not null
       drop table #buffer_descriptor_pages;
go
 
create table #buffer_descriptor_pages
(
       num INT null,
       ParentObject varchar(400) not null,
       [Object] varchar(400) not null,
       Field varchar(400) not null,
       VALUE varchar(400) not null
);
 
 
declare @sql varchar(max) = ;
declare @database_id int;
declare @file_id int;
declare @page_id int;
declare @i int = 0;
 
declare c_pages cursor fast_forward
for
select
       database_id, file_id, page_id
from sys.dm_os_buffer_descriptors
where database_id = db_id(‘db_test’)
       and page_type = ‘DATA_PAGE’
             and is_modified = 1;
 
open c_pages;
 
fetch next from c_pages into @database_id, @file_id, @page_id;
 
while @@fetch_status = 0
begin
 
       set @sql = ‘dbcc traceon (3604); dbcc page(‘ + cast(@database_id as varchar(10))
                                                + ‘, ‘ + cast(@file_id as varchar(10))
                                                                          + ‘, ‘ + cast(@page_id as varchar(10))
                                                                          + ‘, 3) with tableresults’;
 
       insert into #buffer_descriptor_pages(ParentObject, [Object], Field, VALUE)
       exec(@sql);
      
       update #buffer_descriptor_pages
       set num = @i
       where num is null;
 
       set @i = @i + 1;
       fetch next from c_pages into @database_id, @file_id, @page_id;    
end
      
close c_pages;
deallocate c_pages;
select *
from #buffer_descriptor_pages
where num in(select num
                           from #buffer_descriptor_pages
                           where (Field = ‘Metadata: ObjectId’)
                                  and VALUE = object_id(‘dbo.test_CCI’))
       and(Field = ‘m_pageId’ or Field = ‘Record Type’ or Field = ‘CD array entry’ or Field = ‘id’ or Field = ‘col1’);

 

blog_55_-_10-_CCI_after_modifying_data

 

Note that this time, I only focused on the modified / dirty pages in my result and I noticed that there are two data pages. The second page (1:94) in the order of this result set is pretty obvious because it concerns the record with id = 1 and col1 = toto (the modified data). However, I’m not sure to know exactly what the first page is but I can again speculate: we performed an update operation and we know that this operation is split in two basic operations DELETE + INSERT. So my feeling here is that this page lies to the deleted bitmap. Let’s have a look at the sys.column_store_row_groups DMV:

 

select
       object_name(object_id) as table_name,
       index_id,
       row_group_id,
       delta_store_hobt_id,
       state_description as [state],
       total_rows,
       deleted_rows,
       size_in_bytes
from sys.column_store_row_groups
where object_id = object_id(‘test_CCI’);

 

blog_55_-_11-_CCI_rowgroup_after_modifying_data

 

And as expected we can notice a logical deleted record in the row group with a new open delta store (and its deleted bitmap). So let’s perform a checkpoint and clear the buffer pool.

 

checkpoint;
go
 
dbcc dropcleanbuffers;
go

 

Now, we can wonder how SQL Server will retrieve data from id column = 1. Indeed, we have a deleted record into the row group from one side and the new version of the data in the delta store on the other side. So, we can guess that SQL Server will need to fetch both the data pages from the delta store and the deleted bitmap to get the correct record.

Let’s verify by performing this test and taking a look first at the memory cache entries related to the columnstore index.

 

blog_55_-_12-_CCI_memory_management_cache_entries_3

 

SQL Server has fetched the corresponding segment (object_type =1) and the deleted bitmap (object_id=6) as well. Note that segment elimination is not performed for the concerned segment because SQL Server is not able to perform an elimination for segments that contain logical deletions.

Finally let’s retrieve the data pages in the buffer pool related to the columnstore index:

 

blog_55_-_13_-_CCI_page_compressed_details

 

Ok we retrieved the same clean pages (is_modified = 0) and performing the same test after rebuilding the CCI yielded an empty result. In the latter case, this is the expected behaviour because rebuilding the columnstore index get rid of deleted records inside the segments. Thus, SQL Server doesn’t need the deleted bitmap.

I didn’t cover all the scenarios in this blog post and some questions are not answered yet. My intention was just to introduce some interesting internal stuff done by the CCI. This is definitely a very interesting topic that I want to cover in the near future. Please feel free to share your thoughts about this exciting feature!

By David Barbarin