Columnstore Indexes – part 119 (“In-Memory Columnstore Location”)

Continuation from the previous 118 parts, the whole series can be found at

This is a rather simple blog post that is dedicated to the theme of the In-Memory Columnstore Indexes location. This has been a constant topic of discussion over a long period of time, even during the public events – and there is a need to clear out this topic.

I have assumed that the In-Memory Columnstore structures (Segments, Dictionaries, …) are located in the In-Memory, but there have been voices that I greatly respect, pointing that actually the Columnstore Object Pool is the exact location of any Columnstore structures, and there is nothing better than to take this feature for a ride and see what the SQL Server engine is actually doing.

For the tests, I took a simple SQL Server 2017 EXPRESS Edtion instance in its RTM version, because of its memory limitations that define that the Memory: Maximum memory-optimized data per database and the Memory: Maximum Columnstore segment cache per instance are both capped at the 352 MB, making the demos on the subject very easy to produce and observe. 🙂

The good old free database ContosoRetailDW will be used for the tests because we do want to make sure that our tables fit into the memory of the test instance – the maximum size for the buffer pool is still 1GB, but the SQL Server Express is free – yes, really free!

The standard script for restoring the database from the C:\Install location and storing this time in C:\DATA\XE is presented below:

Let us add the In-Memory capability to our test database, using the C:\Data\XE\Contosoxtp folder for the In-Memory storage:

We need to drop the primary key on the FactOnlineSales table and create a Clustered Columnstore Index on this table:

Now, let us create a simple copy of our Columnstore FactOnlineSales table, that will be storing In-Memory data, and which shall be called FactOnlineSales_Hekaton:

For the simplest test, let’s load 1 Million Rows into our In-Memory table, making sure that no data is staying in the Delta-Store, but compressed in the Row Group:

With the help of my CISL free library, you should have very similar results for the output of the dbo.cstore_GetRowGroups and the dbo.cstore_GetRowGroupsDetails functions, giving an overview and the details on the available Row Groups – 1 Million Rows that all stored inthe compressed Row Group, while the Tail Row Group (Delta-Store) is currently empty.

Since we have data successfully loaded into the InMemory Columnstore Index, let us discover where it is stored – and for that purpose, we can use the sys.dm_clumn_store_object_pool DMV, that was introduced in the SQL Server 2016 – and I will simply ask to return the names of the tables, with the data in the Columnstore Object Pool:

The results are quite clear – this DMV reports that only On-Disk Columnstore Index from the table FactOnlineSales are represented in this DMV. The FactOnlineSales_Hekaton table has nothing stored within it – giving the very first impression that the In-Memory Columnstore Index is using In-Memory structures, but to confirm it we shalle need to make a couple more steps.

Running CISL cstore_GetMemory function with the parameter @tableName = ‘FactOnlineSales_Hekaton’ produces no results, meaning that the DMV sys.dm_os_memory_cache_entries contains no information on the Columnstore structures for this table.

Let’s dive into the sys.dm_db_xtp_memory_consumers and the sys.memory_optimized_tables_internal_attributes DMVs to discover if the In-Memory structures are holding the information on the In-Memory Columnstore Indexes structures:

To make sure I was seeing everything correctly, I went looking into the memory_clerks:

At this point I thought the following – let’s waste it totally, by adding the 10 million rows and it will prove that the In-Memory … But then I have looked at the number above and realised that we were already above the announced limits of 352 MB…

But I thought – who cares and so here we go:

Let’s check on our memory clerks, this time including the Columnstore Object Pool as well:

5 GB for the In-Memory on SQL Server 2017 Express Edition, right …

Running queries against the In-Memory Columnstore table will not push the data into the Columnstore Object Pool and this is my number one argument for confirming that the In-Memory Columnstore is not using the Columnstore Object Pool.

The Final Thoughts

Either this is a bug of the DMVs and the Columnstore Object Pool is the real user of the In-Memory Columnstore Indexes (makes no sense, seriously dangerous, because 10 databases would waste 352 MB limit in a second and then the In-Memory Columnstore would not be an In-Memory technology),
(most probably) Houston, there is a huge bug in the Express Edition of SQL Server 2017 right now and the In-Memory Columnstore is stored in the In-Memory.

to be continued with Columnstore Indexes – part 120 (“Merge Replication 2016-2017”)

Leave a Reply

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