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

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:

Creating a list of fixed drives and volume information
Reviewing SQL Server In-memory container details using XTP system stored procedures
Determining used/free space within the SQL Server In-Memory database
Building HTML Emails with SQL Server and XML
Checking out XTP Performance counters
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.

SELECT SUBSTRING(fixed_drive_path, 0, 2) Drive,

drive_type_desc [Description],

CONVERT(DECIMAL(18, 2), free_space_in_bytes / 1024.0 / 1024.0 / 1024) AS [AvailableSpace(GB)]

FROM sys.dm_os_enumerate_fixed_drives WITH(NOLOCK);

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

SELECT

@@SERVERNAME Server,

Volume,

CAST(SizeGB as DECIMAL(10,2)) CapacityGB,

CAST((SizeGB - FreeGB) as DECIMAL(10,2)) UsedGB,

CAST(FreeGB as DECIMAL(10,2)) FreeGB,

CAST([%Free] as DECIMAL(10,2))[%Free]

FROM(

SELECT distinct(volume_mount_point) Volume,

(total_bytes/1048576)/1024.00 as SizeGB,

(available_bytes/1048576)/1024.00 as FreeGB,

(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as '%Free'

FROM sys.master_files AS f CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

group by volume_mount_point, total_bytes/1048576,

available_bytes/1048576

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.

USE SQLShackInMemDB

EXEC sp_spaceused @updateusage = 'FALSE', @mode = 'ALL', @oneresultset = '1', @include_total_xtp_storage = '1';

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.

SELECT SUM(xtp_precreated) xtp_precreated,

SUM(xtp_used) xtp_used,

SUM(xtp_pending_truncation) xtp_pending_truncation

FROM

(

SELECT SUM(file_size_in_bytes / 1024) xtp_precreated,

0 xtp_used,

0 xtp_pending_truncation

FROM sys.dm_db_xtp_checkpoint_files

WHERE state_desc = 'PRECREATED'

UNION ALL

SELECT 0 xtp_precreated,

SUM(file_size_in_bytes / 1024) xtp_used,

0 xtp_pending_truncation

FROM sys.dm_db_xtp_checkpoint_files

WHERE state_desc != 'PRECREATED'

AND state_desc != 'WAITING FOR LOG TRUNCATION'

UNION ALL

SELECT 0 xtp_precreated,

0 xtp_used,

SUM(file_size_in_bytes / 1024) xtp_pending_truncation

FROM sys.dm_db_xtp_checkpoint_files

WHERE state_desc = 'WAITING FOR LOG TRUNCATION'

) T;

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

USE SQLShackInMemDB;

(

SELECT COUNT(1)

FROM sys.data_spaces

WHERE TYPE = 'FX'

) > 0

BEGIN

SELECT DB_NAME(DB_ID()) Database_Name,

[TYPE] = A.TYPE_DESC,

[FILE_Name] = A.name,

[FILEGROUP_NAME] = fg.name,

[File_Location] = A.PHYSICAL_NAME,

[FILESIZE_MB] = CONVERT(DECIMAL(10, 2), A.SIZE / 128.0),

[USEDSPACE_MB] = CASE

WHEN CONVERT(DECIMAL(10, 2), A.SIZE / 128.0-((SIZE / 128.0)-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) IS NULL

THEN T.[Used Space GB]

ELSE CONVERT(DECIMAL(10, 2), A.SIZE / 128.0-((SIZE / 128.0)-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))

END,

[FREESPACE_MB] = CASE

WHEN CONVERT(DECIMAL(10, 2), A.SIZE / 128.0-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) IS NULL

THEN t.[Free Space GB]

ELSE CONVERT(DECIMAL(10, 2), A.SIZE / 128.0-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)

END,

[FREESPACE_%] = CASE

WHEN CONVERT(DECIMAL(10, 2), ((A.SIZE / 128.0-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE / 128.0))*100) IS NULL

THEN T.[Percent Used]

ELSE CONVERT(DECIMAL(10, 2), ((A.SIZE / 128.0-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE / 128.0))*100)

END,

[AutoGrow] = 'By '+CASE is_percent_growth

WHEN 0

THEN CAST(growth / 128 AS VARCHAR(10))+' MB -'

WHEN 1

THEN CAST(growth AS VARCHAR(10))+'% -'

ELSE ''

END+CASE max_size

WHEN 0

THEN 'DISABLED'

WHEN-1

THEN ' Unrestricted'

ELSE ' Restricted to '+CAST(max_size / (128) AS VARCHAR(10))+' MB'

END+CASE is_percent_growth

WHEN 1

THEN ' [autogrowth by percent, BAD setting!]'

ELSE ''

END

FROM sys.database_files A

LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id

LEFT JOIN

(

SELECT [Name] AS [Logical Name],

ISNULL([DATA], 0) + ISNULL([DELTA], 0) + ISNULL([ROOT], 0) AS [Used Space GB],

[FREE] AS [Free Space GB],

CAST((ISNULL([DATA], 0) + ISNULL([DELTA], 0) + ISNULL([ROOT], 0)) / (ISNULL([DATA], 0) + ISNULL([DELTA], 0) + ISNULL([FREE], 0) + ISNULL([ROOT], 0)) * 100 AS DECIMAL(5, 2)) AS [Percent Used]

FROM

(

SELECT CF.container_id AS FileID,

CF.file_type_desc AS FileType,

CAST(ROUND(SUM(file_size_in_bytes) / 1024.0 / 1024 / 1024, 3) AS DECIMAL(9, 3)) AS FileSizeGB,

DF.[name] AS [Name],

DF.physical_name AS [FileName]

FROM sys.dm_db_xtp_checkpoint_files AS CF

INNER JOIN sys.database_files AS DF ON DF.file_id = CF.container_id

GROUP BY DF.[file_id],

file_type_desc,

CF.container_id,

DF.[name],

DF.physical_name

) AS Base PIVOT(SUM(FileSizeGB) FOR FileType IN([DATA],

[DELTA],

[FREE],

[ROOT])) AS Pvt

) T ON T.[Logical Name] = a.name;

END;

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:

Select CF.container_id As [File ID],

Cast(Sum(file_size_in_bytes)/1024.0/1024 As DECIMAL(9,3)) As [File Size GB],

DF.[name] As [Logical Name],

DF.physical_name As [Container]

From sys.dm_db_xtp_checkpoint_files As CF

Inner join sys.database_files As DF On DF.file_id = CF.container_id

Group By DF.[file_id], CF.container_id, DF.[name], DF.physical_name

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

SELECT CF.container_id As [FileID],

CF.file_type_desc As [FileType],

CAST(ROUND(SUM(file_size_in_bytes)/1024.0/1024, 3) As DECIMAL(9,3)) As [FileSizeMB],

DF.[name] As [Logical Name],

DF.physical_name As [Container]

FROM sys.dm_db_xtp_checkpoint_files As CF

INNER JOIN sys.database_files As DF On DF.file_id = CF.container_id

GROUP BY DF.[file_id], CF.container_id, CF.file_type_desc,DF.[name],DF.physical_name

ORDER BY FILE_ID

Merge the Container information with its corresponding drives:

SELECT

@@SERVERNAME Server,

Volume,

CAST(SizeGB as DECIMAL(10,2)) CapacityGB,

CAST((SizeGB - FreeGB) as DECIMAL(10,2)) UsedGB,

CAST(FreeGB as DECIMAL(10,2)) FreeGB,

CAST([%Free] as DECIMAL(10,2))[%Free],

T2.*

FROM(

SELECT distinct(volume_mount_point) Volume,

(total_bytes/1048576)/1024.00 as SizeGB,

(available_bytes/1048576)/1024.00 as FreeGB,

(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as '%Free'

FROM sys.master_files AS f CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

group by volume_mount_point, total_bytes/1048576,

available_bytes/1048576

)T1

inner join

(

Select FileID As [File ID],

[Name] As [Logical Name],

[FileName] As [BaseContainer],

-- substring([FileName],0,4),

isnull([DATA],0) + isnull([DELTA],0) +isnull([FREE],0) + isnull([ROOT],0) As [Total Size GB],

isnull([DATA],0) + isnull([DELTA],0) + isnull([ROOT],0)As [Used Space GB],

[FREE] As [Free Space GB],

cast((isnull([DATA],0) + isnull([DELTA],0) + isnull([ROOT],0))/ ( isnull([DATA],0) + isnull([DELTA],0) +isnull([FREE],0) + isnull([ROOT],0))*100 as decimal(5,2)) As [Percent Used],

DB_ID() As [Database ID]

From (Select CF.container_id As FileID,

CF.file_type_desc As FileType,

Cast(Round(Sum(file_size_in_bytes)/1024.0/1024/1024, 3) As Decimal(9,3)) As FileSizeGB,

DF.[name] As [Name],

DF.physical_name As [FileName]

From sys.dm_db_xtp_checkpoint_files As CF

Inner join sys.database_files As DF On DF.file_id = CF.container_id

Group By DF.[file_id],file_type_desc, CF.container_id, DF.[name], DF.physical_name) As Base

Pivot (Sum(FileSizeGB)

For FileType In ([DATA], [DELTA], [FREE], [ROOT])) As Pvt

)T2

on T1.Volume=substring(T2.[BaseContainer],0,4)

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.

--Declare the variable

DECLARE

@body varchar(max),

@Header varchar(max),

@HTML varchar(max)

Set @Header = '<html><head>' +

'<style>' +

'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

'</style>' +

'</head>' +

'<body><table cellpadding=2 cellspacing=2 border=2>' +

'<tr bgcolor=#FFEFD3><td align=center>Server Name</td>' +

'<td align=center>Volume</td>' +

'<td align=center>CapacityGB</td>' +

'<td align=center>UsedGB</td>' +

'<td align=center>FreeGB</td>'+

'<td align=center>%Free</td>'+

'<td align=center>FileID</td>'+

'<td align=center>LogicalName</td>'+

'<td align=center>BaseContainer</td>'+

'<td align=center>TotalSizeGB</td>'+

'<td align=center>UsedSpaceGB</td>'+

'<td align=center>FreeSpaceGB</td>'+

'<td align=center>FreeSpaceGB</td></tr>'

-- return output

Select @Body=(

SELECT

@@SERVERNAME As [TD],

Volume As [TD],

CAST(SizeGB as DECIMAL(10,2)) As [TD],

CAST((SizeGB - FreeGB) as DECIMAL(10,2)) As [TD],

CAST(FreeGB as DECIMAL(10,2)) As [TD],

CAST([%Free] as DECIMAL(10,2))As [TD],

T2.[FileID] As [TD],

T2.[LogicalName]As [TD],

T2.[BaseContainer] As [TD],

T2.[TotalSizeGB] As [TD],

T2.[UsedSpaceGB] As [TD],

T2.[FreeSpaceGB] As [TD],

T2.[PercentUsed] As [TD]

FROM(

SELECT distinct(volume_mount_point) Volume,

(total_bytes/1048576)/1024.00 as SizeGB,

(available_bytes/1048576)/1024.00 as FreeGB,

(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as '%Free'

FROM sys.master_files AS f CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

group by volume_mount_point, total_bytes/1048576,

available_bytes/1048576

)T1

inner join

(

Select FileID As [FileID],

[Name] As [LogicalName],

[FileName] As [BaseContainer],

-- substring([FileName],0,4),

isnull([DATA],0) + isnull([DELTA],0) +isnull([FREE],0) + isnull([ROOT],0) As [TotalSizeGB],

isnull([DATA],0) + isnull([DELTA],0) + isnull([ROOT],0)As [UsedSpaceGB],

[FREE] As [FreeSpaceGB],

cast((isnull([DATA],0) + isnull([DELTA],0) + isnull([ROOT],0))/ ( isnull([DATA],0) + isnull([DELTA],0) +isnull([FREE],0) + isnull([ROOT],0))*100 as decimal(5,2)) As [PercentUsed],

DB_ID() As [DatabaseID]

From (Select CF.container_id As FileID,

CF.file_type_desc As FileType,

Cast(Round(Sum(file_size_in_bytes)/1024.0/1024/1024, 3) As Decimal(9,3)) As FileSizeGB,

DF.[name] As [Name],

DF.physical_name As [FileName]

From sys.dm_db_xtp_checkpoint_files As CF

Inner join sys.database_files As DF On DF.file_id = CF.container_id

Group By DF.[file_id],file_type_desc, CF.container_id, DF.[name], DF.physical_name) As Base

Pivot (Sum(FileSizeGB)

For FileType In ([DATA], [DELTA], [FREE], [ROOT])) As Pvt

)T2

on T1.Volume=substring(T2.[BaseContainer],0,4)

For XML raw('tr'), Elements

)

Select @HTML = @Header + @body + '</table></body></html>';

PRINT @HTML

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:

SELECT object_name as ObjectName, counter_name as CounterName,

cntr_value, cntr_type

FROM sys.dm_os_performance_counters WHERE object_name LIKE '%XTP%';

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.

CREATE TABLE dbo.InMemorySQLshackDemo

(ID INT,

AuthorName VARCHAR(100) NULL,

CONSTRAINT PK_InMemorySQLshackDemo_ID PRIMARY KEY NONCLUSTERED HASH(ID)

WITH(BUCKET_COUNT = 10000)

) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

INSERT INTO [dbo].InMemorySQLshackDemo

([ID],

AuthorName

)

VALUES

(1,

'Prashanth Jayaram'

);

SELECT *

FROM InMemorySQLshackDemo;

SET NOCOUNT ON;

DECLARE @id INT= 100;

BEGIN TRAN;

WHILE @id < 500

BEGIN

INSERT INTO InMemorySQLshackDemo(id,

AuthorName)

VALUES(@id,

'Prashanth');

SET @id = @id + 1;

END;

WAITFOR DELAY '00:00:15';

COMMIT TRAN;

SET NOCOUNT ON;

DECLARE @id INT= 5000;

BEGIN TRAN;

WHILE @id < 5050

BEGIN

INSERT INTO InMemorySQLshackDemo(id,

AuthorName)

VALUES(@id,

'Prashanth');

SET @id = @id + 1;

END;

WAITFOR DELAY '00:00:15';

ROLLBACK TRAN;

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.

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

Retrieve SQL Server In-Memory Container information

How to determine used/free space within In Memory databases

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

Performance Counters

Summary

Table of contents

Related posts: