{"id":5055,"date":"2015-07-10T16:46:57","date_gmt":"2015-07-10T14:46:57","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/"},"modified":"2015-07-10T16:46:57","modified_gmt":"2015-07-10T14:46:57","slug":"clustered-columnstore-index-and-memory-management","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/","title":{"rendered":"Clustered columnstore index and memory management"},"content":{"rendered":"

Few weeks ago, I had the opportunity to give a session about the clustered columnstore index feature (CCI) at our In-Memory event<\/a> 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).<\/p>\n

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.<\/p>\n

First of all, let\u2019s 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:<\/p>\n

 <\/p>\n

select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 memory_node_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 pages_kb,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 page_size_in_bytes<\/span><\/div>\n
from sys.dm_os_memory_clerks<\/span><\/span><\/span><\/span><\/span><\/div>\n
where type = ‘CACHESTORE_COLUMNSTOREOBJECTPOOL’;<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
go<\/span><\/div>\n

 <\/p>\n

\"blog_55_-_2-_CCI_memory_management_memory_clerk-_\"<\/p>\n

 <\/p>\n

First scenario<\/span><\/strong><\/p>\n

We\u2019ll see how SQL Server behaves by reading data exclusively from the delta store. Let\u2019s begin with a pretty simple table:<\/p>\n

 <\/p>\n

— create table test_CCI<\/span><\/div>\n
if object_id(‘test_CCI’, ‘U’) is not null<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 drop table test_CCI;<\/span><\/span><\/span><\/span><\/div>\n
go<\/span><\/div>\n
\u00a0<\/span><\/div>\n
create table test_CCI<\/span><\/span><\/span><\/div>\n
(<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id int not null identity(1,1),<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 col1 char(10) not null default ‘col_’<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
);<\/span><\/div>\n
go<\/span><\/div>\n

 <\/p>\n

Next, let\u2019s create a CCI that will include 1 compressed row group and 1 delta store (open state):<\/p>\n

 <\/p>\n

set nocount on;<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
— insert 1000 rows<\/span><\/div>\n
insert test_CCI default values;<\/span><\/span><\/span><\/span><\/span><\/div>\n
go 1000<\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
— create CCI <\/span><\/div>\n
create clustered columnstore index [PK__test_CCI__3213E83F3A6FE3AC]<\/span><\/span><\/span><\/span><\/span><\/div>\n
on test_CCI <\/span><\/span><\/div>\n
go<\/span><\/div>\n
\u00a0<\/span><\/div>\n
— insert 1 rows in order to create a delta store (OPEN state)<\/span><\/div>\n
insert test_CCI default values;<\/span><\/span><\/span><\/span><\/span><\/div>\n
go 1<\/span><\/span><\/div>\n

 <\/p>\n

Let\u2019s have a look at the CCI row group\u2019s information:<\/p>\n

 <\/p>\n

select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name(object_id) as table_name,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 index_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 row_group_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 delta_store_hobt_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 state_description as [state],<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 total_rows,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deleted_rows,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 size_in_bytes<\/span><\/div>\n
from sys.column_store_row_groups<\/span><\/span><\/span><\/span><\/div>\n
where object_id = object_id(‘test_CCI’);<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
go<\/span><\/div>\n

 <\/p>\n

\"blog_55_-_2-_CCI_configuration-_\"<\/p>\n

 <\/p>\n

Let\u2019s execute the first query that will fetch data from the record to the delta store<\/p>\n

 <\/p>\n

dbcc dropcleanbuffers;<\/span><\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max(id)<\/span><\/span><\/span><\/span><\/div>\n
from dbo.test_CCI<\/span><\/span><\/span><\/div>\n
where id = 1001<\/span><\/span><\/span><\/div>\n

 <\/p>\n

Let\u2019s have a look at the memory cache entries related to the CCI memory pool:<\/p>\n

 <\/p>\n

select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 in_use_count,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 is_dirty,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 pages_kb,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 entry_data,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 pool_id<\/span><\/div>\n
from sys.dm_os_memory_cache_entries<\/span><\/span><\/span><\/span><\/span><\/div>\n
where type = ‘CACHESTORE_COLUMNSTOREOBJECTPOOL’;<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
go<\/span><\/div>\n

 <\/p>\n

\"blog_55_-_3-_CCI_memory_management_cache_entries_empty-_\"<\/p>\n

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:<\/p>\n

 <\/p>\n

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

 <\/p>\n

And after looking at the extended event file, I noticed that the segment was eliminated by SQL Server as expected.<\/p>\n

 <\/p>\n

\"blog_55_-_4-_CCI_segment_elimination\"<\/p>\n

 <\/p>\n

The hobt_id value relies to the compressed segment in the columnstore index:<\/p>\n

 <\/p>\n

select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 partition_id,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 hobt_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 column_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 segment_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 row_count,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 min_data_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max_data_id<\/span><\/div>\n
from sys.column_store_segments <\/span><\/span><\/span><\/span><\/div>\n
where hobt_id = 72057594041925632<\/span><\/span><\/span><\/div>\n

 <\/p>\n

\"blog_55_-_5-_CCI_segment\"<\/p>\n

 <\/p>\n

Second scenario<\/span><\/strong><\/p>\n

It will consist in reading data directly from the columnstore segment.<\/p>\n

 <\/p>\n

dbcc dropcleanbuffers<\/span><\/span><\/div>\n
go<\/span><\/div>\n
\u00a0<\/span><\/div>\n
select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max(id)<\/span><\/span><\/span><\/span><\/div>\n
from dbo.test_CCI<\/span><\/span><\/span><\/div>\n
where id = 1<\/span><\/span><\/span><\/div>\n

 <\/p>\n

With the previous script that uses the sys.dm_os_memory_cache_entries<\/em> DMV we can see this time, two cached entries from the columnstore object pool:<\/p>\n

 <\/p>\n

\"blog_55_-_1-_CCI_memory_management_-_cache_entries\"<\/p>\n

 <\/p>\n

 <\/p>\n

I would like to thank Sunil Argarwal<\/a> (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:<\/p>\n

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

So let\u2019s 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\u2019s 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<\/em> column in the table dbo.test_CCI<\/em>. I performed some other tests and they let me think that the column_id from the entry_data<\/em> 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.<\/p>\n

Moreover, according to the Sunil\u2019s 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.<\/p>\n

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?<\/p>\n

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

<\/div>\n
select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 page_type,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 count(*) as nb_pages<\/span><\/span><\/span><\/span><\/div>\n
from sys.dm_os_buffer_descriptors<\/span><\/span><\/span><\/span><\/span><\/div>\n
where database_id = db_id(‘db_test’)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
group by page_type<\/span><\/span><\/span><\/div>\n
order by page_type<\/span><\/span><\/span><\/div>\n

 <\/p>\n

\"blog_55_-_7-_CCI_buffer_pool\"<\/p>\n

 <\/p>\n

Ok, there are plenty of pages in the buffer pool related to the db_test<\/em> database. Let\u2019s focus first on data page by using the following script that consists in retrieving data pages only for the dbo.test_CCI<\/em> table:<\/p>\n

 <\/p>\n

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

 <\/p>\n

In my case, I retrieved only one page with the following detail:<\/p>\n

 <\/p>\n

\"blog_55_-_7-_CCI_page_compressed_details\"<\/p>\n

 <\/p>\n

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.<\/p>\n

Next, let\u2019s have a look at the LOB pages (our segments)<\/p>\n

 <\/p>\n

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

 <\/p>\n

\"blog_55_-_8-_CCI_LOB_pages\"<\/p>\n

 <\/p>\n

We have one LOB page (TEXT_MIXPAGE type) but it seems to be empty and I admit that I don\u2019t have any idea of this page. I will update this blog post later if I get a response.<\/p>\n

So, to summarize and according to my tests, it\u2019s seems that SQL Server reads LOB pages directly from the columnstore object pool and doesn\u2019t need to use the BP in this case.<\/p>\n

 <\/p>\n

Third scenario<\/span><\/strong><\/p>\n

It will consist in updating data from the columnstore index and understanding how SQL Server behaves in this case.<\/p>\n

 <\/p>\n

alter index [PK__test_CCI__3213E83F3A6FE3AC]<\/span><\/span><\/span><\/div>\n
on [dbo].[test_CCI] rebuild<\/span><\/span><\/span><\/span><\/div>\n

…<\/p>\n

select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name(object_id) as table_name,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 index_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 row_group_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 delta_store_hobt_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 state_description as [state],<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 total_rows,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deleted_rows,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 size_in_bytes<\/span><\/div>\n
from sys.column_store_row_groups<\/span><\/span><\/span><\/span><\/div>\n
where object_id = object_id(‘test_CCI’);<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n

 <\/p>\n

\"blog_55_-_9-_CCI_without_deltastore\"<\/p>\n

 <\/p>\n

Next, let\u2019s update the columnstore index by using the following query:<\/p>\n

<\/div>\n
checkpoint;<\/span><\/span><\/div>\n
go<\/span><\/div>\n
\u00a0<\/span><\/div>\n
dbcc dropcleanbuffers;<\/span><\/span><\/span><\/div>\n
go<\/span><\/div>\n
\u00a0<\/span><\/div>\n
update dbo.test_CCI<\/span><\/span><\/span><\/div>\n
set col1 = ‘toto’<\/span><\/span><\/span><\/span><\/div>\n

 <\/p>\n

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\u2019s have a look at the modified pages related to the columnstore index.<\/p>\n

 <\/p>\n

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

 <\/p>\n

\"blog_55_-_10-_CCI_after_modifying_data\"<\/p>\n

 <\/p>\n

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<\/em> = 1 and col1<\/em> = toto (the modified data). However, I\u2019m 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\u2019s have a look at the sys.column_store_row_groups<\/em> DMV:<\/p>\n

 <\/p>\n

select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name(object_id) as table_name,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 index_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 row_group_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 delta_store_hobt_id,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 state_description as [state],<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 total_rows,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deleted_rows,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 size_in_bytes<\/span><\/div>\n
from sys.column_store_row_groups<\/span><\/span><\/span><\/span><\/div>\n
where object_id = object_id(‘test_CCI’);<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n

 <\/p>\n

\"blog_55_-_11-_CCI_rowgroup_after_modifying_data\"<\/p>\n

 <\/p>\n

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\u2019s perform a checkpoint and clear the buffer pool.<\/p>\n

 <\/p>\n

checkpoint;<\/span><\/span><\/div>\n
go<\/span><\/div>\n
\u00a0<\/span><\/div>\n
dbcc dropcleanbuffers;<\/span><\/span><\/span><\/div>\n
go<\/span><\/div>\n

 <\/p>\n

Now, we can wonder how SQL Server will retrieve data from id <\/em>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.<\/p>\n

Let\u2019s verify by performing this test and taking a look first at the memory cache entries related to the columnstore index.<\/p>\n

 <\/p>\n

\"blog_55_-_12-_CCI_memory_management_cache_entries_3\"<\/p>\n

 <\/p>\n

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.<\/p>\n

Finally let\u2019s retrieve the data pages in the buffer pool related to the columnstore index:<\/p>\n

 <\/p>\n

\"blog_55_-_13_-_CCI_page_compressed_details\"<\/p>\n

 <\/p>\n

Ok we retrieved the same clean pages (is_modified = 0<\/em>) 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\u2019t need the deleted bitmap.<\/p>\n

I didn\u2019t 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!<\/p>\n

By David Barbarin<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":26,"featured_media":5056,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[579,526,51,52],"type_dbi":[],"class_list":["post-5055","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-column-store","tag-datawarehouse","tag-sql-server","tag-sql-server-2014"],"acf":[],"yoast_head":"\nClustered columnstore index and memory management - dbi Blog<\/title>\n<meta name=\"description\" content=\"Clustered columnstore index and memory management\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Clustered columnstore index and memory management\" \/>\n<meta property=\"og:description\" content=\"Clustered columnstore index and memory management\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-07-10T14:46:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"689\" \/>\n\t<meta property=\"og:image:height\" content=\"40\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"Clustered columnstore index and memory management\",\"datePublished\":\"2015-07-10T14:46:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/\"},\"wordCount\":2481,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg\",\"keywords\":[\"Column Store\",\"DataWareHouse\",\"SQL Server\",\"SQL Server 2014\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/\",\"name\":\"Clustered columnstore index and memory management - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg\",\"datePublished\":\"2015-07-10T14:46:57+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"description\":\"Clustered columnstore index and memory management\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg\",\"width\":689,\"height\":40},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/clustered-columnstore-index-and-memory-management\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Clustered columnstore index and memory management\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/microsoft-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Clustered columnstore index and memory management - dbi Blog","description":"Clustered columnstore index and memory management","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/","og_locale":"en_US","og_type":"article","og_title":"Clustered columnstore index and memory management","og_description":"Clustered columnstore index and memory management","og_url":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/","og_site_name":"dbi Blog","article_published_time":"2015-07-10T14:46:57+00:00","og_image":[{"width":689,"height":40,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"Clustered columnstore index and memory management","datePublished":"2015-07-10T14:46:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/"},"wordCount":2481,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg","keywords":["Column Store","DataWareHouse","SQL Server","SQL Server 2014"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/","url":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/","name":"Clustered columnstore index and memory management - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg","datePublished":"2015-07-10T14:46:57+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"description":"Clustered columnstore index and memory management","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_55_-_2-_CCI_memory_management_memory_clerk-_.jpg","width":689,"height":40},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/clustered-columnstore-index-and-memory-management\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Clustered columnstore index and memory management"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5055","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5055"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5055\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/5056"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5055"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5055"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5055"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5055"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}