Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

A-Z of In-Memory OLTP : Monitoring Memory Usage

Posted by blakhani on January 16, 2014


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

 

image

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.

image

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:

image

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

image

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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • One Response to “A-Z of In-Memory OLTP : Monitoring Memory Usage”

    1. […] update or delete to generate stale rows so far. Here is the memory used by table. Query available here.   Now, let’s generate garbage rows and check the DMVs again. We are going to delete 1000 […]

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: