How Do I Show Memory Usage From A Columnstore Index in SQL Server?

Columnstore indexes in SQL Server are a great feature from an analytic and data warehouse perspective—you can get great compression ratios and batch mode processing allows for great performance. One of the ways these performance gains happen is by columnstore indexes bypassing the 8k pages in the SQL Server bufferpool (mostly—I’ll touch on that in a bit). Columnstore indexes get their own area of memory—which uses large pages (which are stored adjacently) to support the underlying lobs that the columnstore index is stored in. So I was curious to see how much memory my columnstore index was actually using—before I knew better, I looked in the buffer cache, which returned:

This shows my index has a 106 pages in the buffer cache—which is interesting because columnstore indexes aren’t supposed to use that right? So I take a look at sys.dm_os_buffer_descriptors:

select
*
from
sys.dm_os_buffer_descriptors
where database_id=6 and allocation_unit_id=72057594085113856;

So there are some pages in the buffer pool from the columnstore—I suspect these are pages from the columnstore dictionary, but I can’t say for certain. So how do we find our actual columnstore memory usage? After chasing around DMVs a bit, I centered on sys.dm_os_memory_cache_entries:

select
*
from
sys.dm_os_memory_cache_entries
where type=CACHESTORE_COLUMNSTOREOBJECTPOOL

Unfortunately, none of the columns in that DMV tie back directly to a specific object or partition id. Except for entry_data—which presents data in this wonderful format (and not it’s not XML, that would have been easier):

<object_type=’1′ db_id=’12’ hobt_id =’72057594042646528′ column_id=’30’ object_id =’0′ object_size=’22760’/>

I knew if I could get that hobt_id, I could show the memory utilization of the columnstore. So with much thanks to at Jeremiah Peschka (b|t) who helped me work on some of the myriad of string functions and concatenation that I had to do to make this work, I present columnstorebuffers.sql. This procedure will return the name of the source object (the table here) and the amount of memory it’s using in the columnstore object pool:

Note—you’ll have to run this procedure on a database level, even though dm_os_memory_cache_entries is global, the other views that join to bring back the name of the object are not.