1

I have only memory optimized table in my database. When I am running below query, I am getting pages_mb for that DB as 710 MB.

SELECT type  
 , name  
 , memory_node_id  
 , pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'

But when I run below query, I am getting mem_alloc_total_mb for that table as 2 and others as 0.

   SELECT
   object_name(object_id) AS table_name,
   memory_allocated_for_indexes_kb / 1024 as mem_alloc_index_mb,
   memory_allocated_for_table_kb / 1024 as mem_alloc_table_mb,
   memory_used_by_indexes_kb / 1024 as mem_used_index_mb,
   memory_used_by_table_kb / 1024 as mem_used_table_mb,
   (memory_allocated_for_table_kb + memory_allocated_for_indexes_kb) / 1024 as mem_alloc_total_mb,
   (memory_used_by_table_kb + memory_used_by_indexes_kb) /1024 as mem_used_total_mb
   FROM sys.dm_db_xtp_table_memory_stats
   where object_id = object_id('<table_name>');
   go

Data from both the above queries are contradicting each other. Am I missing anything?

NOTE: Durability setting for the memory optimized tabe is "SCHEMA_AND_DATA".

1 Answer 1

1

Memory for In-Memory OLTP is under the sp_configure setting for max memory, but is not part of the buffer pool. Not sure if that helps or not. You could have a lot of stale rows that are waiting until there is memory pressure in order to be "garbage collected". I wrote a script that's part of Brent Ozar's First Responder Kit that might be of some help to you, located here:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_BlitzInMemoryOLTP.sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.