SQL Server In-Memory database internal memory structure monitoring

We can use a variety of available options to keep systems performing at their very best. The SQL Server In-Memory OLTP technological advancement is a great innovation to relieve tempdb contentions. This feature is available with an additional advantage to monitor systems without incurring stress on the database servers.

This article details:

The use of the SMSS standard report to get a quick view of the available resources
How T-SQL can be used to monitor SQL Server In-Memory databases
Details the internal structure of memory consumers
Drive space management within the SQL Server using DMFs
Monitor In-Memory Containers from within SQL Server
How to get the details of In-Memory checkpoint mode
The determination of used/free space within the In-Memory database
And more…

Introduction

As technology advances and the landscape shifts the relational database transactions from non-volatile to volatile spaces, the complexity of database monitoring and maintenance become more intense (and sometimes, tedious). This makes database monitoring and the evaluation of database metrics important. The purpose of monitoring and evaluation is to track the implementation and outputs systematically and measure the effectiveness of the standards, in order to determine exactly what change is needed and when.

Getting Started

Let us see how the SSMS Standard Report can be used to get high-level details of SQL Server in-memory objects. The standard SSMS report, “Memory Usage by Memory Optimized Objects” shows the memory usage details of SQL Server In-Memory optimized objects.

To view the Memory Usage by Memory Optimized Objects report:

In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Right-click Databases, and then click Reports
Select Memory Usage By Memory Optimized Objects

The three sections of the Memory Usage by Memory Optimized Objects are listed below

Total memory allocated to memory optimized objects

Using the DMV sys.dm_db_xtp_table_memory_stats we can derive the total memory allocated to Memory Optimized objects.

The Below SQL generates the output which can also be generated from the SSMS reports.

(

SELECT COUNT(1)

FROM sys.data_spaces

WHERE type = 'FX'

) > 0

BEGIN

SELECT SUM([Total used Memory MB] + [Total Unused Memory MB] + [Index used Memory MB] + [Index Unused Memory MB]) [Total memory allocated to memory optimized objects MB]

FROM

(

SELECT OBJECT_NAME(object_id) AS tblName,

CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total used Memory MB],

CAST(memory_allocated_for_table_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total Unused Memory MB],

CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index used Memory MB],

CAST(memory_allocated_for_indexes_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index Unused Memory MB]

FROM sys.dm_db_xtp_table_memory_stats

) T;

END;

Total memory usage by memory optimized objects, a pie chart showing memory consumption of table, index and system.

Memory usage details of SQL Server In-Memory tables

We can also use the related DMV sys.dm_db_xtp_table_memory_stats to draw the sizing details of the memory optimized tables.

(

SELECT COUNT(1)

FROM sys.data_spaces

WHERE type = 'FX'

) > 0

BEGIN

SELECT OBJECT_NAME(object_id) AS tblName,

CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total used Memory MB],

CAST(memory_allocated_for_table_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total Unused Memory MB],

CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index used Memory MB],

CAST(memory_allocated_for_indexes_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index Unused Memory MB]

FROM sys.dm_db_xtp_table_memory_stats;

END;

Memory consumption by internal system structure components

To see the total memory consumed by system allocators, use the following query:

SELECT SUM(allocated_bytes) / (1024 * 1024) AS total_allocated_MB,

SUM(used_bytes) / (1024 * 1024) AS total_used_MB

FROM sys.dm_xtp_system_memory_consumers;

Next, let us take a look at the different memory consumer types.

SELECT memory_consumer_type,

memory_consumer_type_desc,

allocated_bytes / 1024 [allocated_bytes_kb],

used_bytes / 1024 [used_bytes_kb],

allocation_count

FROM sys.dm_xtp_system_memory_consumers;

In the following output, we can see there are a few memory LOOKASIDE consumers.

Note: A lookaside consumer is part of MMU (Memory Management Unit) and it’s a type of buffer cache used to reduce the time taken to access the memory location of each transactions.

To see the memory consumption at run-time when accessing SQL Server In-Memory optimized tables, use the following query

SELECT type clerk_type,

name,

memory_node_id,

pages_kb / 1024 pages_mb

FROM sys.dm_os_memory_clerks

WHERE type LIKE '%xtp%';

Property	DataType	Description
name	nvarchar(256)	Specifies the internally assigned name of this memory clerk. A component can have several memory clerks of a specific type. A component might choose to use specific names to identify memory clerks of the same type. It’s a not nullable column.
memory_node_id	smallint	It returns the ID of the memory node. It is also a not nullable field.
pages_kb	bigint	Applies to: SQL Server 2012 through SQL Server 2017.
pages_kb	bigint	Specifies the amount of page memory allocated in kilobytes (KB) for this memory clerk. It’s not nullable column

The output shows that the databases ID 13 reserve a 173 KB in the memory.

Now, let’s see what are the other components of SQL Server using the memory internally using sys.dm_os_memory_clerks this will give you the overview of active components of memory clerks within SQL Server.

SELECT TOP (10) type MemoryClerkType,

SUM(pages_kb) / 1024 AS SizeMB

FROM sys.dm_os_memory_clerks

GROUP BY type

ORDER BY 2 DESC;

SQL Server In-Memory optimized checkpoint mode

To get details of SQL Server In-Memory check point mode of a SQL instance from the Error log, run the following query that will list the memory optimized Checkpoint Mode of an SQL instance

1	EXEC sys.xp_readerrorlog 0, 1, N'In-Memory OLTP initialized on';

The output can list any of the three possible values

In-Memory OLTP initialized on low-end machine
In-Memory OLTP initialized on standard machine
In-Memory OLTP initialized on high-end machine (Large Checkpoint mode)

The aforementioned query, xp_readerrorlog, reads the SQL Server error log file. During the start up the SQL Server is going to figure out whether or not the system is using large checkpoints. When there is a restart, every time, SQL Server starts up, it checks for the system configuration and specifications, and it also quickly measure how can it read the SQL Server In-Memory OLTP checkpoint file. So when I run the query, it gives me a text message stating it is initialized on a low-end machine, and the reason for that is because I have system configured with a very less resources.

Note: The trace flag 9912 enabled to get the system configured to use large checkpoint mode. In any of the case it is advised to measure a performance that suits better with your workload.

If the output shows SQL Server In-Memory OLTP initialized on a high-end machine, then it must satisfy the following mandatory points

The server must has 16 or more logical processors
It must be configured with 128GB or more memory
High transaction rate systems that is >200 MB/Sec IO.

Note:

SQL Server 2016 CU1 and/or later, global trace lag 9912 must be enabled to get large checkpoint mode.
The large checkpoints are designed for high throughput and high transaction rate systems.
If any one or two of the aforementioned statements are true, then the output will display “ In-Memory OLTP initialized on standard machine”
If all of the three statement are true then the SQL instance is configured with “In-Memory OLTP initialized on high-end machine”
If none of the three statement are holds good, then it returns the output “In-Memory OLTP initialized on low-end machine”

How to check XTPSupported at the instance level

The useful information about your instance can be seen by running the following query. It uses SERVERPROPERTY, a built-in function to list the property.

1	SELECT SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported]

Monitor a SQL Server In-Memory container

The DMV sys.dm_db_xtp_checkpoint_files provides information about checkpoint file pairs in the database, using two types of files: the data file and the delta file. The data file stores the inserted records, while the delta stores the removed records. These records are merged over time, using garbage collection principles. To see each of the files with details, use the sys.dm_db_xtp_checkpoint_files extended store procedure.

sp_spaceused – sp_spaceused can now be used to understand the storage footprint associated with SQL Server In-Memory OLTP. A new parameter, ‘@include_total_xtp_storage’, has been added to the stored procedure to get the relevant data.

USE SQLShackInMemDB;

EXEC sp_spaceused

@updateusage = 'FALSE',

@mode = 'ALL',

@oneresultset = '1',

@include_total_xtp_storage = '1';

xtp_precreated, xtp_used and xtp_pending_truncation are based on the state of the checkpoint file pairs. These pointers give information about the requirement of database backup initiated or not. This is how the XTP columns are derived in the sp_spaceused output.

Summary

In this article, we’ve we covered a number of useful SQL Server in-memory queries, and how to interpret the results of those queries. I will discuss more about drive space used by memory containers and XTP performance counters in my next article. I hope you will like this article. Feel free to leave the feedback below in the comment section.

SQL Server In-Memory database internal memory structure monitoring

How to monitor internal data structures of SQL Server In-Memory database objects

Author
Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021