Help: SQL Server

Monitoring is a human nature to be safe and avoid and unexpected failure. Whenever we drive a car at high speed, its human nature to have a look at speedometer. Whenever you go to visit doctor, he would do some monitoring of certain parameters and then ask relevant question to troubleshoot the problem which you tell him. (Oh God! Troubleshooting has become part of my examples, even for doctor!)

In SQL Server, how do you normally monitor space used by a table? Generally we use sp_spaceused and provide table name which gives good amount of details. But interestingly enough, the store procedure would NOT work for in-memory tables as shown below.

select count(*) as 'count' 
from [dbo].[MyFirstMemporyOptimizedTable]
go
sp_spaceused 'MyFirstMemporyOptimizedTable'
go

Our table MyFirstMemporyOptimizedTable has good amount of rows and can’t be using zero space as reported by sp_spaceused. In order to get the memory used by in-memory tables, we need to use another DMV called dm_db_xtp_table_memory_stats. Now, how do I write a query? I generally look for the UI interface or standard report which gives me the data and then use profiler to find the nicely formatted query.

I was able to find a standard report for memory usage of in-memory tables called “Memory Usage By Memory Optimized Objects” as shown below.

I launched the report and captured profiler to get the query. Here is the query which can get us the data for all IMO tables in the database. (captured by profiler, not written by me)

 SELECT t.object_id
    ,t.NAME
    ,ISNULL((
            SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_used_by_table_kb) / 1024.00)
            ), 0.00) AS table_used_memory_in_mb
    ,ISNULL((
            SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_allocated_for_table_kb - TMS.memory_used_by_table_kb) / 1024.00)
            ), 0.00) AS table_unused_memory_in_mb
    ,ISNULL((
            SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_used_by_indexes_kb) / 1024.00)
            ), 0.00) AS index_used_memory_in_mb
    ,ISNULL((
            SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_allocated_for_indexes_kb - TMS.memory_used_by_indexes_kb) / 1024.00)
            ), 0.00) AS index_unused_memory_in_mb
FROM sys.tables t
INNER JOIN sys.dm_db_xtp_table_memory_stats TMS ON (t.object_id = TMS.object_id)

Here is the output:

If we look at report, we would get exactly same output but in more “presentable” format as below.

If we look at the bottom area of report, it’s the output of earlier query.

If you find other ways to monitor the memory usage, write it in comment section and share with the world.