Prashanth Jayaram

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

February 25, 2019 by

This is the continuation of the previous article SQL Server In-Memory database internal memory structure monitoring. In this article we are going to dissect the details of other components that are available to validate the SQL Server In-Memory objects.

In this article we’ll detail:

  1. Creating a list of fixed drives and volume information
  2. Reviewing SQL Server In-memory container details using XTP system stored procedures
  3. Determining used/free space within the SQL Server In-Memory database
  4.  Building HTML Emails with SQL Server and XML
  5. Checking out XTP Performance counters
  6.   And more…

Getting Started

SQL Server In-Memory OLTP solutions will improve system performance, simply stated, by moving data into memory. It helps to improve the performance of query execution. In this guide, let’s dive more deeply into these internal data structures.

Fixed drive and volume information

We have a couple of useful queries that will pull back information about all the drives that are visible to the operating system, and then all the drives that have SQL Server database files on them. So, we get information about all the drives that are visible to the operating system, and it just shows the space available for each drive that is available to the operating system whether or not it has any SQL Server database files on it.

We also have a separate query that gives us the size and free space for all your logical drives that have any SQL Server database files on them, and this is useful information because you don’t want to run out of space, and also as you get low on space, performance of system can be degraded. It has a direct impact on the IO operation. So going further into this, fixed drives shows you the available space on all the logical drives available to the operating system but the DMF sys.dm_os_volume_stats gives you the volume information that includes total size and the available space for all the logical drives that have any SQL Server database files, and that’s very useful information to know, and it’s going to help you detect when you’re running low on disk space

The output of the SQL is given below.

Note: It is evident that running low on disk space can affect your performance with both magnetic and with flash storage.

Note: The older version of SQL Server uses xp_fixeddrives extended stored procedure to enumerate the disk usage information in the SQL Server. The output is then manipulated by enabling OLE-enabled objects to get the total size of the drive. Then, the data is transformed to get the required results. The process is a little cumbersome and is not recommended for various security reasons. From SQL Server 2008 SP1, the DMF sys.dm_os_volume_stats() can be used to derive the space-related metrics from the SQL instances.

Retrieve SQL Server In-Memory Container information

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.

The procedures, 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.

How to determine used/free space within In Memory databases

SQL Server In memory database consists of various components. The database must have s MEMORY_OPTIMIZED_DATA filegroup. This filegroup may contain one or more containers. Every container stores data and/or delta files.

To get the files/containers, auto-growth setting of each file/container information, use the below SQL

The following result shows you the space usage details of SQL Server In-Memory database files

To get the storage details of usage by SQL Server In-Memory OLTP, use this query:

To see a breakdown of storage utilization by state and file type run the following query:

Merge the Container information with its corresponding drives:

Building HTML Emails using SQL Server In-Memory data structures and XML

Let’s build an email to send notification to intended recipients. There are many ways we can generate the HTML tags dynamically but the use of XML elements makes most of the tag preparation a lot easier.

The code can be further simplified easily to send email using db mail configuration.

Performance Counters

The SQL Server In-Memory OLTP engine provides performance counters to help you with monitoring and troubleshooting. You can run the query below to see the currently-available performance counters:

SQL Server provides objects and counters that can be used by Performance Monitor to monitor SQL Server In-Memory OLTP activity. The objects and counters are shared across all instances of a given version of SQL Server on the machine, starting in SQL Server 2014 (12.x).

Note:

In the prior version the performance object/counter names used to begin with XTP for SQL Server In-memory objects. Now starting with SQL Server 2016 (13.x), the names are like the following pattern:

SQL Server <version> XTP Transaction Log

In this case, its SQL Server 2016 XTP Transaction Log

Run the following SQL to validate and monitor the counters using Perfmon.

Note:

SQL Server In-Memory database undo transaction is not logged. In this case, we can see a small spike for savepoint rollback transaction.

Summary

So far, we’ve covered how to run and analyze SQL Server In-Memory data structures. We’ve walk-through how to interpret the volume and container results using SQL Server XTP DMV’s, so you can better understand what the results actually mean. Stay tuned for more updates… Feel free to leave the comments below.

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

Prashanth Jayaram

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
Prashanth Jayaram
In-Memory OLTP, Monitoring

About 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

548 Views