Monitor Memory Consumption for SQL Server Memory Optimized Tables

By: Daniel Farina   |   Comments   |   Related: > In Memory OLTP

Problem

As a DBA you need to monitor the size of SQL Server database objects like tables and indexes, but how do you do this for SQL Server Memory-Optimized tables. In this tip I will show you how.

Solution

A very common task for a SQL Server DBA is to check table sizes and take statistics about how database space is distributed amongst objects. We need to know the biggest tables of our database and how much they are growing. But with Memory-Optimized tables we have another variable to take into consideration: Memory consumption by tables and indexes.

Maybe for disk based tables this task can be overlooked relying on disk space alerts, but for Memory-Optimized tables not monitoring object size could be more problematic. Especially if you are using the standard version of SQL Server, in that case you don't have the Resource Governor Feature to set up a Resource Pool for Memory-Optimized Objects.

Fortunately SQL Server 2014 provides us with two new Dynamic Management Views to monitor memory usage for Memory-Optimized Objects which I will describe and provide a few examples about how to use them.

Sample SQL Server Database Creation

Let's create a sample database including Memory Optimized Tables so we can learn about this new DMV.

USE [master]
GO

CREATE DATABASE [SampleDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SampleDB_file1', 
   FILENAME = N'E:\MSSQL\SampleDB_1.mdf',
   SIZE = 128MB , 
   MAXSIZE = UNLIMITED, 
   FILEGROWTH = 64MB), 
 FILEGROUP [SampleDB_MemoryOptimized_filegroup] 
   CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'SampleDB_MemoryOptimized',
    FILENAME = N'E:\MSSQL\SampleDB_MemoryOptimized',
    MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'SampleDB_log_file1',
    FILENAME = N'E:\MSSQL\SampleDB_1.ldf',
    SIZE = 64MB,
    MAXSIZE = 2048GB,
    FILEGROWTH = 32MB)
GO

Test SQL Server Memory Optimized Table

The next script will create a simple Memory-Optimized table.

USE SampleDB
GO

IF OBJECT_ID('dbo.SampleTable','U') IS NOT NULL
    DROP TABLE dbo.SampleTable
GO

CREATE TABLE SampleTable
    (
      ID INT IDENTITY(1,1),
      SDate DATETIME NOT NULL ,
      TextCol VARCHAR(2000) ,
      NumCol DECIMAL(30, 4)

        CONSTRAINT PK_SampleTable
        PRIMARY KEY NONCLUSTERED HASH ( id ) 
   WITH ( BUCKET_COUNT = 262144 ),

   INDEX IX_SDate (SDate) 
    ) WITH (MEMORY_OPTIMIZED =
   ON,
   DURABILITY = SCHEMA_AND_DATA)
GO

Generate Test SQL Server Data

Now the final step, after this we are ready to begin our tests.

USE SampleDB
GO

INSERT INTO  SampleTable
SELECT  
        DATEADD(DAY,
                CAST (RAND(CONVERT (INT, CONVERT(VARBINARY, NEWID()))) * 10 AS INT),
                GETDATE()) ,
        CAST(REPLICATE(CHAR(CAST(RAND(CONVERT (INT, CONVERT(VARBINARY, NEWID())))
                            * 255 AS INT)),
                       CAST (RAND(CONVERT (INT, CONVERT(VARBINARY, NEWID())))
                       * 2000 AS INT)) AS VARCHAR(2000)) ,
        CAST(RAND(CONVERT (INT, CONVERT(VARBINARY, NEWID()))) * 255 AS DECIMAL(30,
                                                              4))

GO 262144

Issue with sp_spaceused for SQL Server Memory Optimized Tables

As far back as I can remember, we have had the sp_spaceused system stored procedure to get information about how much space a table and its indexes are using.

USE SampleDB
GO

EXEC sp_spaceused N'SampleTable', 'TRUE';
GO

But for Memory-Optimized Tables, as you can see in the image below, this stored procedure doesn't give us much information.

New DMV sys.dm_db_xtp_table_memory_stats

The sys.dm_db_xtp_table_memory_stats Dynamic Management View gives us memory usage statistics for every Memory-Optimized table in the current database. Remember that Dynamic Management Views containing the "db" prefix has a database scope, so you should query them in the context of the database that you want to analyze.

In order to query this DMV you need be granted VIEW DATABASE STATE permission, otherwise you can only get information about the tables you have grant SELECT permissions on.

Let's do a SELECT and see the results.

USE SampleDB
GO

SELECT  object_id ,
        OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) [Table_Name] ,
        memory_allocated_for_table_kb ,
        memory_used_by_table_kb ,
        memory_allocated_for_indexes_kb ,
        memory_used_by_indexes_kb
FROM    sys.dm_db_xtp_table_memory_stats

This is a screen capture of the output of the previous query in my test environment.

The output gives us the memory size in Kb of the allocated and used space by tables and its indexes. As we can see, the results resemble those of sp_spaceused for disk based tables. The rows with negative values for object_id represent system objects used by the Hekaton Engine for its functionality.

I must remark that the four columns (memory_allocated_for_table_kb, memory_used_by_table_kb, memory_allocated_for_indexes_kb and memory_used_by_indexes_kb) include the size of row versions.

Also there is a standard report on SSMS named "Memory Usage by Memory Optimized Objects" that draws the output of this DMV into a chart.

Maybe at this point you may be thinking that with this Dynamic Management View we have the whole picture about how much memory our Memory-Optimized objects are using. Well, that is not the case, because this DMV does not include any information about Table Variable Types. If we want that information we need to take a look at the sys.dm_db_xtp_memory_consumers Dynamic Management View.

New SQL Server DMV sys.dm_db_xtp_memory_consumers

With the sys.dm_db_xtp_memory_consumers Dynamic Management View we have memory information about Memory-Optimized objects at a more granular level.

The DMV in question introduces the concept of "Memory Consumers" in order to differentiate the memory structures used by the Hekaton Engine.

Here is a table describing each of the Memory Consumers

The following query will give us information about the memory used by each Consumer. Due to the fact that this DMV returns the allocated and used sizes expressed in bytes I converted those values to kilobytes so we can compare results with the previous query on sys.dm_db_xtp_table_memory_stats.

USE SampleDB
GO

SELECT  memory_consumer_id ,
        memory_consumer_type_desc ,
        memory_consumer_desc ,
        object_id ,
        OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) [Table_Name] ,
        index_id ,
        CAST(allocated_bytes / 1024. AS NUMERIC(15, 2)) [allocated_kb] ,
        CAST(used_bytes / 1024. AS NUMERIC(15, 2)) [used_kb] 
FROM    sys.dm_db_xtp_memory_consumers  

 

On the next image we can see the output of the previous query.

Now let's create a Table Type so we can see what this Dynamic Management View returns when we create a Table Variable.

USE SampleDB
GO

IF TYPE_ID('dbo.SampleType') IS NOT NULL
    DROP TYPE dbo.SampleType
GO

CREATE TYPE dbo.SampleType AS TABLE
    (
      ID INT NOT NULL,
      SDate DATETIME NOT NULL ,
      TextCol VARCHAR(2000) ,
      NumCol DECIMAL(30, 4)
        PRIMARY KEY NONCLUSTERED HASH ( id ) 
   WITH ( BUCKET_COUNT = 262144 ),

   INDEX IX_SDate (SDate) )

 WITH (MEMORY_OPTIMIZED = ON)
GO

I created an execution loop to insert data into a Table Variable. Run this script and while it is running, in another window execute the query to sys.dm_db_xtp_memory_consumers and look at the value of Database 64K Page Pool.

USE SampleDB
GO

DECLARE @MyTable AS [SampleType]
DECLARE @i INT = 0

WHILE @i < 5
    BEGIN
        INSERT  INTO @MyTable
                SELECT  ID ,
                        SDate ,
                        TextCol ,
                        NumCol
                FROM    dbo.SampleTable 
                WITH (SNAPSHOT)

        SELECT  @i = @i + 1

    END

The next image is a screen capture of the query using sys.dm_db_xtp_memory_consumers in my test environment. As you can see the size of Database 64K Page Pool has increased.

Next Steps

• In this tip you have an overview of the New SQL Server 2014 Dynamic Management Views.
• Take a look at Dynamic Management Views and Functions Tips Category.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips