sys.dm_db_xtp_table_memory_stats – Day 75 – One DMV a Day

Hello Geeks and welcome to the Day 75 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.

So far in memory optimized related DMVs I have covered transactions, checkpoints, hash index, merge and garbage collection related DMVs. Today I will cover another In-Memory related DMV. If someone asks for the size details of a table we are very used to using sp_spaceused. The problem is this does not work with In-Memory tables. And so another DMV for my series, sys.dm_db_xtp_table_memory_stats.

SP_SPACEUSED inMem_tbl
GO

sys.dm_db_xtp_table_memory_stats sp_spaceused

The advantage with sys.dm_db_xtp_table_memory_stats is we can see the details of all In-Memory tables at one go. We have to do a little bit of programming if we need to do this for disk based tables. So the output of this DMV will be as below.

SELECT OBJECT_NAME(object_id) AS tblName,
	memory_allocated_for_table_kb,
	memory_used_by_table_kb,
	memory_allocated_for_indexes_kb,
	memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats

sys.dm_db_xtp_table_memory_stats output

The output is straight forward for sys.dm_db_xtp_table_memory_stats. It gives the memory allocated and memory used by the table and its indexes.

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

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 →

One Comment on “sys.dm_db_xtp_table_memory_stats – Day 75 – One DMV a Day”

Leave a Reply to Rick Willemain Cancel reply

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