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…
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.
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.123456789101112131415161718IF(SELECT COUNT(1)FROM sys.data_spacesWHERE type = 'FX') > 0BEGINSELECT 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.1234567891011121314IF(SELECT COUNT(1)FROM sys.data_spacesWHERE type = 'FX') > 0BEGINSELECT 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
Next, let us take a look at the different memory consumer types.
allocated_bytes / 1024 [allocated_bytes_kb],
used_bytes / 1024 [used_bytes_kb],
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,
pages_kb / 1024 pages_mb
WHERE type LIKE '%xtp%';
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.
It returns the ID of the memory node. It is also a not nullable field.
Applies to: SQL Server 2012 through SQL Server 2017.
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
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
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.
- 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.
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.
@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.
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.
Table of contents
|SQL Server In-Memory database internal memory structure monitoring|
|How to monitor internal data structures of SQL Server In-Memory database objects|
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