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

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

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