Setup a Memory Quota for SQL Server Memory Optimized Databases

By: Daniel Farina   |   Comments (2)   |   Related: > In Memory OLTP

Problem

You successfully entered into the In-Memory OLTP world by the hand of SQL Server 2014.  After a while you notice that the In-Memory objects are using more memory than you estimated. In this tip I will explain how to proceed.

Solution

This problem may become a hot spot when you plan to adopt the new In-Memory OLTP features of SQL Server 2014. Remember that Hekaton, the In-Memory OLTP engine, is embedded into SQL Server, and both engines shares the same resources.

Of course, it allows us to interact with both engines with minimal changes to existing applications. But for DBAs it implies some changes in the way we administer our instances. The fact that both engines use the same process has two consequences:

1. A limit to the instance memory, sets a boundary to the amount of memory available for both engines
2. Both engines are independent of each other and they can compete for resources

Maybe you heard about SQL Server’s configuration options "Max Server Memory (MB)" and "Min Server Memory (MB) ", they have been available for a long time. We use the first to set a hard limit to our instance’s memory consumption and the second to define a baseline of memory SQL Server won’t give up to the operating system in case the OS needs to page out memory.

Since version 2008 of SQL Server we can limit the amount of memory and CPU a session can use by creating a resource pool and creating a classifier function to map the session to a workload group.

Now in SQL Server 2014 we have the possibility to create resource pools and bind databases with Memory-Optimized objects into them.

Memory Available for Memory-Optimized SQL Server Objects

To prevent disk based workloads from running out of memory, SQL Server sets up a limit for the memory consumption of In-Memory objects according to available resources on the underlying resource pool, which can be the default pool or a user created one. What I mean is that even if you create a dedicated pool for Memory-Optimized objects SQL Server will establish a threshold onto it for Memory-Optimized objects according to the instance target committed memory which you can check by querying the committed_target_kb value of sys.dm_os_sys_info Dynamic Management View.

Here is a table showing the percentage of memory you can use for Memory-Optimized objects according to the memory assigned to a given pool, either a user created one or the default.

I created the following query to do the math.

USE master 
GO

;WITH    cte
  AS ( SELECT   RP.pool_id ,
  RP.Name ,
  RP.min_memory_percent ,
  RP.max_memory_percent ,
  CAST (RP.max_memory_kb / 1024. / 1024. 
    AS NUMERIC(12, 2)) AS max_memory_gb ,
  CAST (RP.used_memory_kb / 1024. / 1024. 
    AS NUMERIC(12, 2)) AS used_memory_gb ,
  CAST (RP.target_memory_kb / 1024. / 1024. 
    AS NUMERIC(12,2)) AS target_memory_gb,
  CAST (SI.committed_target_kb / 1024. / 1024. 
    AS NUMERIC(12, 2)) AS committed_target_kb 
    FROM     sys.dm_resource_governor_resource_pools RP
    CROSS JOIN sys.dm_os_sys_info SI
  )
SELECT  c.pool_id ,
  c.Name ,
  c.min_memory_percent ,
  c.max_memory_percent ,
  c.max_memory_gb ,
  c.used_memory_gb ,
  c.target_memory_gb ,  
  CAST(c.committed_target_kb  *
  CASE WHEN c.committed_target_kb <= 8 THEN 0.7
    WHEN c.committed_target_kb < 16 THEN 0.75
    WHEN c.committed_target_kb < 32 THEN 0.8
    WHEN c.committed_target_kb <= 96 THEN 0.85
    WHEN c.committed_target_kb > 96 THEN 0.9
  END * c.max_memory_percent /100 AS NUMERIC(12,2))
   AS [Max_for_InMemory_Objects_gb]
FROM    cte c
 

Create a SQL Server Database and Bind a Resource Pool

Now in this example we are going to create a database and bind it to a Resource Pool. First we create our sample database, the one we will add to a resource pool.

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

Now we create a sample table, so we can later fill it up with data to test the resource pool we are going to create next.

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),
      TextCol CHAR(8000) ,


        CONSTRAINT PK_SampleTable
        PRIMARY KEY NONCLUSTERED HASH ( id ) 
   WITH ( BUCKET_COUNT = 262144 )
    ) WITH (MEMORY_OPTIMIZED =
   ON,
   DURABILITY = SCHEMA_AND_DATA)
GO

Creating the Resource Pool for SQL Server In-Memory Objects

In order to set up a quota for Memory-Optimized objects we need to create a Resource Pool and configure the settings MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT taking into consideration what I previously explained about the percentage of available Target Pool memory. You may be asking yourself, if we want to establish a quota for memory consumption, why do we have to configure the MIN_MEMORY_PERCENT setting?  The answer to that question is we have to be cautious about memory pressure from the other pools we may have configured. The MIN_MEMORY_PERCENT value establishes a baseline about how much memory we need to have reserved for our Pool whereas MAX_MEMORY_PERCENT sets a barrier we cannot trespass.  As a best practice Microsoft recommends to establish both MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT to the same value when creating a Pool for Memory-Optimized Objects.

Let’s create the Resource Pool.

USE master
GO

CREATE RESOURCE POOL [InMemoryObjects] 
  WITH 
    ( MIN_MEMORY_PERCENT = 50, 
    MAX_MEMORY_PERCENT = 50 );
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Binding the Database to the Resource Pool

To bind our database to the Resource Pool we created in the last step we have to execute the sys.sp_xtp_bind_db_resource_pool system Stored Procedure passing the database and pool name as parameters. Take a look.

USE master
GO

EXEC sp_xtp_bind_db_resource_pool 'SampleDB', 'InMemoryObjects'
GO

The next image is the output of the previous query.

But we are not ready yet. As you can see on the previous image, in order to make the binding effective we need to set our database offline and then back online as follows.

USE master
GO

ALTER DATABASE SampleDB SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE SampleDB SET ONLINE
GO

The next image is a screen capture showing the output of the first query in this tip, in which you can see that our previously created pool is empty.

Now we are going to insert data in our test table, so we can see the memory usage of our recently created pool.

USE SampleDB
GO

INSERT INTO dbo.SampleTable 
        ( TextCol )
SELECT REPLICATE('a', 8000)
GO 60000

As you can see on the image below, the resources for our table were allocated from the InMemoryObjects Resource Pool we created for this matter.

Reconfiguring SQL Server Memory Limits

After our database was added to a Resource Pool it could be the case that you may need to change the memory reservation for Memory-Optimized Objects. In this case there is no need to reload our database by setting it offline and back online.

I will show you how easily we can reconfigure memory limits either by altering our Resource Pool configuration or by changing the "Max Server Memory (MB)" server parameter.

The next script will change the percent limit of memory our pool can use.

USE master
GO

ALTER RESOURCE POOL [InMemoryObjects]
WITH
     ( MIN_MEMORY_PERCENT = 65,
       MAX_MEMORY_PERCENT = 65 ) 
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

As you can see on the next image, the new limit for our Pool is 65 percent.

Now I am going to change the "Max Server Memory (MB)" server parameter on my test environment to use 10 GB of memory at most. You may need to change the value according to the amount of RAM you have. I want to remark that in a real production environment, you should also change the value of "Min Server Memory (MB)" server parameter accordingly.

USE master
GO

EXEC sys.sp_configure 'max server memory (MB)', 10240
RECONFIGURE WITH OVERRIDE

The next image shows us that changing the "Max Server Memory (MB)" parameter automatically increases the amount of memory available for Memory-Optimized objects.

Unbinding the SQL Server Database from the Resource Pool

If we want to unbind a database from a Resource Pool we also have to execute a System Stored Procedure, in this case sys.sp_xtp_unbind_db_resource_pool with the peculiarity that it only receives the database name as a parameter.

Here is an example showing how to unbind our test database from the Resource Pool we have created for this tip.

USE master
GO

EXEC sp_xtp_unbind_db_resource_pool 'SampleDB'
GO

For the unbinding to take effect we do need to set the database offline and then back online.

USE master
GO

ALTER DATABASE SampleDB SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE SampleDB SET ONLINE
GO

As you can see on the next image, after reloading the database it doesn’t allocate resources of the InMemoryObjects Pool.

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.
• Read this tip for a detailed list of Dynamic Management Views related to Resource Governor: Using DMVs to Adjust SQL Server Resource Governor Settings.
• In this tip you will see how to configure Resource Governor to handle Workloads: Handling workloads on SQL Server 2008 with Resource Governor.

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