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 14^th September 2013.

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.

Memory_consumer_type_desc –
    VARHEAP (variable length heap)
    HASH (index)
    PGPOOL (one consumer for runtime operations. E.g.: table variables)
Memory_consumer_desc –
    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,
	memory_consumer_desc,
	object_id,
	index_id,
	allocated_bytes,
	used_bytes,
	allocation_count 
FROM sys.dm_db_xtp_memory_consumers

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,
	xmc.memory_consumer_desc,
	OBJECT_NAME(xmc.object_id) AS objName,
	i.name AS indName,
	xmc.allocated_bytes,
	xmc.used_bytes,
	xmc.allocation_count
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

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,
Manu

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