Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,601,709 hits
  • Select GETDATE()

    April 2019
    M T W T F S S
    « Apr    
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  
  • Advertisements

Posts Tagged ‘sp_spaceused’

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
  • Advertisements

    Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP | Tagged: , , , , , , , , , , , , , , , , | 1 Comment »