sys.dm_db_xtp_memory_consumers – Day 64 – One DMV a Day

Hello Geeks and welcome to the Day 64 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday I have completed extended event related DMVs. Today I will start with the most famous hero in recent times of the SQL Server product. The In-Memory OLTP aka Hekaton. And probably this is going to be my last set of DMVs in this series. Yes, I am going to complete this series after covering the In-Memory related DMVs. I will start with the DMV which answers the first question with In-Memory OLTP. How much space is occupied by my In-Memory tables? Sys.dm_db_xtp_memory_consumers has the answer.

Sys.dm_db_xtp_memory_consumers provides the detailed information on the space occupied by each consumer in the In-Memory OLTP engine. If you have already not known about my connection with In-Memory OLTP please see this. I had delivered the first session ever on In-Memory in India on 14th September 2013.

64 sys.dm_db_xtp_memory_consumers Event


You can see the level 400-500 details in the presentation. I have used the same scripts to create In-Memory tables for these blogs. You can get the scripts from here.

Coming back to the DMV, sys.dm_db_xtp_memory_consumers gives the following important information.

    VARHEAP (variable length heap)
    HASH (index)
    PGPOOL (one consumer for runtime operations. E.g.: table variables)
    VARHEAP – Database heap: used for user data allocations for a database.
        Database system heap: used for non-user data allocations for a database.
        Range index heap: private heap for range indexes.
    HASH – No description.
    PGPOOL – Database 64K page pool – Only one row and one description.
Object_id – Id of the object
Index_id – Id of the index
Allocated_bytes – Bytes reserved for this consumer
Used_bytes – Actual bytes used
Allocation_count – number of allocations for this consumer


Now let us run the simple select on sys.dm_db_xtp_memory_consumers.

SELECT memory_consumer_type_desc,
FROM sys.dm_db_xtp_memory_consumers

sys.dm_db_xtp_memory_consumers All

From the output you can see all the memory consumers including the in-memory table we have created. Let us join sys.dm_db_xtp_memory_consumers with sys.indexes and get the result which we are more interested in.

SELECT xmc.memory_consumer_type_desc,
	OBJECT_NAME(xmc.object_id) AS objName, AS indName,
FROM sys.dm_db_xtp_memory_consumers xmc
INNER JOIN sys.indexes i
ON xmc.index_id = i.index_id
AND xmc.object_id = i.object_id

sys.dm_db_xtp_memory_consumers Table

Now you can see the table which we created and the space used by it. Tomorrow I will be covering another In-Memory optimized tables related DMV. So stay tuned. Till then

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook


About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.