Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Monitor Memory Consumption for SQL Server Memory Optimized Tables


By:   |   Last Updated: 2014-09-22   |   Comments   |   Related Tips: More > 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.

sp_spaceused Execution

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.

Select to sys.dm_db_xtp_table_memory_stats

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.

Output of -Memory Usage by Memory Optimized Objects- Report

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

Consumer ID

Type

Description

0

Aggregation

According to Microsoft, represents the aggregation of two or more Memory Consumer and should not be displayed in the sys.dm_db_xtp_memory_consumers DMV

2

VARHEAP

Represents memory consumed by Structures of Variable size which can be:
Database Heap: The data of tables.
Database System Var Heap: Internal Data of the Hekaton Engine.
Range Index Heap: Memory allocated by BW-Trees whose are the Data Structure used by Hekaton Engine to implement Range Indexes.

3

HASH

Refers to the memory consumption of Hash Indexes.

5

PGPOOL

Includes information about memory consumption of Table Variables and runtime operations

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.

Select to sys.dm_db_xtp_memory_consumers

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.

Database 64K Page Pool Increase
Next Steps


Last Updated: 2014-09-22


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools