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
 

Setup a Memory Quota for SQL Server Memory Optimized Databases


By:   |   Last Updated: 2014-10-31   |   Comments (2)   |   Related Tips: More > 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.

Target Pool Memory

% Available for In-Memory Objects

<= 8 GB

70%

<= 16 GB

75%

<= 32 GB

80%

<= 96 GB

85%

> 96 GB

90%

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.

Execution of sys.sp_xtp_bind_db_resource_pool System Stored Procedure

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.

Resource Pool Usage Before Data Insertion into In-Memory Table

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.

Resource Allocation After Data Insert

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.

Memory Available for In-Memory Objects After Resource Pool Re-Configuration

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.

Memory Available for In-Memory Objects After Server Memory Increase

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.

Resource Pool Usage After Database Unbind
Next Steps


Last Updated: 2014-10-31


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.



    



Thursday, December 18, 2014 - 8:35:41 PM - Daniel Farina Back To Top

Hi Eric!
Thanks again for reading and commenting!
This is a very interesting question. Even when you don't enable Resource Governor, SQL Server still uses it.
Even in the standard edition where you cannot enable the Resource Governor feature SQL Server uses for its internal operations this feature.
Take a look at the screen captures and you will see that there are two resource pools with ids 1 and 2 that i didnt create. Those are the Internal and Default Pools and cannot be deleted.
The internal pool is only used for SQL Server system processes and the dafault pool is left for all the user activity.
So, taking it into consideration, the Defalut Pool is where the memory will be allocated for In-Memory objects.

Thank you!


Thursday, December 18, 2014 - 12:41:28 PM - eric81 Back To Top

If you don't use resource groups how much memory will be allocated by SQL for "In Memory Objects"?


Learn more about SQL Server tools