Monitoring InMemory Objects and Txns – Memory Utilization

For Inmemory objects, the data exists strictly in the memory. The data may or may not be persisted on to the disk depending on the table durability but that does not change the fact the data always stays in the memory. Monitoring Memory Utilization is always key for database systems but it is more important now than ever because Inmemory transactions cannot continue if memory is not available(no spilling\paging to disk ever happens with InMemory transactions). Also, Inmemory transactions use optimistic concurrency, which is achieved by using multiple row versions and all these stay in the memory as well. With all these complexities around, it is very important to monitor the memory utilization.

Using resource governor, we can create a InMemory resource pool, set up max and min memory limits and bind the Inmemory database to the resource pool, can help limiting the Inmemory objects and transactions memory usage.  By setting up appropriate values, this can help not bringing the entire system down(it still can bring the InMemory transactions down).

The bottom line is with InMemory you always need to monitor the memory utilization.

There are two easy ways to do this:

1.Performance Counters DMV:

select * from sys.dm_os_performance_counters
where counter_name ='XTP Memory Used (KB)' and instance_name = '_Total'

2.Using PerfMon Counters

MSSQL$<<InstanceName>>::Databases --> XTP Memory Used(KB) --> _Total

This gives us the total memory utilization by all InMemory objects and transactions across the instance. You can also track the Inmemory memory usage per individual databases.

 

 

 

 

 

Advertisement