In-Memory OLTP – The bind memory pathway a.k.a resource governor

Introduction

Since SQL Server 2014, Resource Governor (RG) has become integrated with the In-Memory OLTP feature to manage workloads and system resource utilization consumption. Now it’s time to dig into this brand-new process and understand the importance of balancing your workload with these new objects. This new configuration process is the main key to protecting SQL Server against undesirable workload latency issues, mainly in your production environment i.e. recovering, redo undo phase and checkpoint process.

Memory-optimized objects are controlled and managed differently than disk-based objects. This new scope of properties bring new precautions that need closer attention, and now the focus is to concentrate on the prime component that these new memory-optimized objects need more: the memory allocation.

Disk-Based tables are managed by buffer pool and data cache. When memory-optimized tables are handled and controlled directly in memory, it implies that memory, more than ever becomes the essential and crucial part of them. Without availability or proper memory configuration on the server that help optimized objects to live, the database will not be able to recover and become online during the recovery process. The lack of memory on an instance that held memory-optimized tables can lead SQL Server to an unsatisfactory OOM (Out of Memory) state.

Memory-optimized objects can be overwhelming and drain the memory of the instance if you don’t apply the proper configuration. This new feature can become the “shot in the foot” of your SQL Server. In order to address this, we need to bind the memory and specify the boundary that memory-optimized objects will have to work. That’s when the Resource Governor appears.

Resource Governor

Resource Governor (RG) is a new technology that appeared on SQL Server 2008 with the main focus to manage SQL Server workloads. RG gives you the possibility to limit CPU, physical IO and incoming memory requests at your SQL Server instance.

Some scenarios are really adept at this technology, such as:

1. Administrative Maintenance Purposes (Backup, Restore, DBCC, Rebuild Operations)
2. Limit End-User Usage
3. Resource Isolation
4. Improve SLAs
5. Isolate and Limit Walkway Query Requests
6. Fine Resource Grain Control
7. Control of Memory-Optimized Objects

Resource Governor with Memory-Optimized Objects

As described before, when memory-objects are added into a database, a new filegroup called MEMORY_OPTIMIZED_DATA must be created. What we need to have in mind is that once this new filegroup comes to live, SQL Server prioritization process change dramatically inside of the engine. New steps are added to ensure and safeguard that these new memory-optimized objects will obey with the ACID [Atomicity, Consistency, Isolation and Durability] properties.

Adding those new objects into the database is not only related to acquire more performance for your end user application is also to certify that your actual workload will not be impacted and start to perform sluggishly, that is the main role of the resource governor, integrate the both worlds and guarantee that they will not collide with each other.

We can benefit with this new possibility now. Create a pool of optimized tables inside of the Resource Governor with the main purpose, create an outstanding and straightforward process that will guarantee the resource control of the memory-objects inside of the SQL Server instance. The memory bind will guarantee reliability and securability without interfering on the existing disk-based tables inside of your database.

The more these memory-optimized objects increase in size, the more memory will become critical, rare and scarce. If we don’t create a barrier between these table styles the memory-optimized objects will push and order the disk-based tables to leave the memory. They’re growing and need more memory to allocate data pages, this is the real problem and now what matters is learning how to control and manage the increasing and growth of these new objects into your environment.

Let’s tackle the problem. First, we will create a database with a memory-optimized filegroup to receive memory-optimized objects and afterward we’re going to add a table to it.

</pre>
USE master
go
CREATE DATABASE inmem_AdventureWorks
ON PRIMARY
(
       NAME = N'inmem_AdventureWorks',
       FILENAME = N'E:\BaseDados\inmem_AdventureWorks.mdf'
),
FILEGROUP inmem_storage CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
(
       NAME = N'FG_inmem_storage',
       FILENAME = N'E:\BaseDados\inmem_storage' ,
       MAXSIZE = UNLIMITED
)
 LOG ON
(
       NAME = N'inmem_AdventureWorks_log',
       FILENAME = N'E:\BaseDados\inmem_AdventureWorks_log.ldf' ,
       FILEGROWTH = 10%
)
GO

Now that we created the database, let’s create the memory-optimized table that will be controlled by the Resource Pool on SQL Server.

USE inmem_AdventureWorks
go
CREATE TABLE inmem_VendasProdutos
(
       [IDSEQUENCE] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
       [SalesOrderID] [int] NOT NULL INDEX idxNCL_inmem_VendasProdutos_SalesOrderID NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
       [CarrierTrackingNumber] [nvarchar](25) NULL,
       [OrderQty] [smallint] NOT NULL,
       [ProductID] [int] NOT NULL,
       [Name] [nvarchar](50) NOT NULL,
       [ProductNumber] [nvarchar](25) NOT NULL,
       [Color] [nvarchar](15) NULL,
       [RowGUIDProduct] [uniqueidentifier] NOT NULL,
       [UnitPrice] [money] NOT NULL,
       [UnitPriceDiscount] [money] NOT NULL,
       [LineTotal] [numeric](38, 6) NOT NULL,
       [RowGUIDOrderDetail] [uniqueidentifier] NOT NULL,
       RowGUIDDados UNIQUEIDENTIFIER DEFAULT NEWID(),
       RowGUIDDados_ii UNIQUEIDENTIFIER DEFAULT NEWID(),
       RowGUIDDados_iii UNIQUEIDENTIFIER DEFAULT NEWID(),
       [ModifiedDate] [datetime] NOT NULL INDEX idxNCL_inmem_VendasProdutos_ModifiedDate NONCLUSTERED
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

We have created the database and table already, at this stage just for demonstration purposes, I will set my “Max Server Memory” server wide configuration with 1024 MB to show a possible OOM (Out of Memory) issue.

</pre>
EXEC master.dbo.SP_CONFIGURE 'max server memory',1024
go
RECONFIGURE
<pre>

Let’s use a disk-based table to populate the memory-optimized object and after the population, we will analyze the amount of memory used by the process checking the “Memory Usage By Memory Optimized Objects” report under Database “inmem_AdvetureWorks” – > Reports – >

</pre>
SELECT SOD.SalesOrderID,
          SOD.CarrierTrackingNumber,
          SOD.OrderQty,
          SOD.ProductID,
          PP.Name,
          PP.ProductNumber,
          PP.Color,
          PP.rowguid AS RowGUIDProduct,
          SOD.UnitPrice,
          SOD.UnitPriceDiscount,
          SOD.LineTotal,
          SOD.rowguid AS RowGUIDOrderDetail,
          SOD.ModifiedDate
INTO inmem_AdventureWorks.dbo.ondsk_VendasProdutos
FROM AdventureWorksBig.Sales.SalesOrderDetail AS SOD
INNER JOIN AdventureWorksBig.Production.Product AS PP
ON SOD.ProductID = PP.ProductID
--121.317
<pre>

The report shows the amount of allocated memory of the memory-optimized tables into the database, normally this is one of the reports that we use to track the total amount of memory usage and based on this sensitive information we change the Resource Pool configuration, please have in mind that underneath this report a Dynamic Management Views is executed to bring this data to us.

(Figure 1 – Memory Usage By Memory Optimized Objects Report.)

You can also monitor the memory usage by consulting the Dynamic Management View called – sys.dm_db_xtp_table_memory_stats.

</pre>
SELECT ms.object_id,
        s.NAME + '.' + t.NAME AS [tablename],
        ms.memory_allocated_for_table_kb,
        ms.memory_used_by_table_kb,
        ms.memory_allocated_for_indexes_kb,
        ms.memory_used_by_indexes_kb
 FROM   sys.dm_db_xtp_table_memory_stats ms
 LEFT OUTER JOIN sys.tables t
 ON ms.object_id = t.object_id
 LEFT OUTER JOIN sys.schemas s
 ON t.schema_id = s.schema_id
 WHERE s.NAME + '.' + t.NAME IS NOT NULL
 ORDER BY ms.memory_allocated_for_table_kb DESC 
<pre>

(Figure 2 – Memory Allocation View.)

At this phase, we have only 17.09 MB of space occupied by memory-optimized objects. Let’s think beyond, the used space will increase considerably with the passage of the time and depend on the OLTP usage, the memory will become scarce and will direct impact the disk-based tables that live inside of this database and all of this because as explained before the memory-optimized objects have prioritization over disk-based tables, they need to live entirely in memory, and this is one of the factors that you must administer and control the growth by creating the Resource Pool control on RG.

Limiting and Monitoring Available Memory of Optimized-Objects

By creating this new control layer, the Resource Governor will bind and limit the memory of the objects that will be allocated on this boundary. Keep in mind that this is a one-time process that you will configure per database, and the best practice recommendation is to create just one resource pool governor to control all the memory-optimized tables that live in your SQL Server instance. Here are the steps that you should follow to success on the configuration.

1. Create Resource Pool with Min and Max Memory (Available Memory of the Instance)

</pre>
CREATE RESOURCE POOL RG_MemoryOptimized
WITH
(
MIN_MEMORY_PERCENT = 1,
MAX_MEMORY_PERCENT = 5
);
GO

2. Alter Resource Governor Status – Reconfigure Process

 
</pre>
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

3. Bind Database into Resource Pool

 
</pre>
EXEC sp_xtp_bind_db_resource_pool 'inmem_AdventureWorks', 'RG_MemoryOptimized'
GO

4. Querying Changes Made

</pre>
SELECT *
FROM sys.resource_governor_resource_pools
SELECT d.database_id, d.name, d.resource_pool_id
FROM sys.databases d
WHERE resource_pool_id IS NOT NULL
GO
<pre>

(Figure 3 – Database inmem_AdventureWorks Bound on RG_MemoryOptimized.)

Since this new possibility arises on Resource Governor I have seen people facing a lot of issues during the configuration process by forgetting the last and most important step, set the database OFFLINE and then turning ONLINE agai. This will enforce the binding of the database and the resource governor pool of the particular database.

USE master
GO
ALTER DATABASE inmem_AdventureWorks SET OFFLINE
GO
ALTER DATABASE inmem_AdventureWorks SET ONLINE
GO
<pre>

Let’s monitor the database and table and check how the resource governor will handle the insert operations on the table – inmem_VendasProdutos.

</pre>
&nbsp;
--Insert - inmem_VendasProdutos
--1
WITH DadosInsert AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CarrierTrackingNumber) AS ID, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,
RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate
FROM dbo.ondsk_VendasProdutos
--121.317
)
INSERT INTO inmem_VendasProdutos (IDSEQUENCE, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,
RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate)
SELECT *
FROM DadosInsert
WHERE ID BETWEEN 1 AND 10000
--10.000
SELECT TOP 10
pool_id
, Name
, min_memory_percent
, max_memory_percent
, max_memory_kb/1024 AS max_memory_mb
, used_memory_kb/1024 AS used_memory_mb
, target_memory_kb/1024 AS target_memory_mb
FROM sys.dm_resource_governor_resource_pools
<pre>

(Figure 4 – Resource Governor Memory Control Process.)

The used_memory_mb and target_memory_mb are the most important pieces of this process that we’re dealing with, note that after the first incoming insert operation the used_memory_mb increased from 0 to 29 MB of usage.

</pre>
&nbsp;
--2
;WITH DadosInsert AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CarrierTrackingNumber) AS ID, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,
RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate
FROM dbo.ondsk_VendasProdutos
)
INSERT INTO inmem_VendasProdutos (IDSEQUENCE, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,
RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate)
SELECT *
FROM DadosInsert
WHERE ID BETWEEN 10001 AND 30000
SELECT TOP 10
pool_id
, Name
, min_memory_percent
, max_memory_percent
, max_memory_kb/1024 AS max_memory_mb
, used_memory_kb/1024 AS used_memory_mb
, target_memory_kb/1024 AS target_memory_mb
FROM sys.dm_resource_governor_resource_pools
<pre>

The second insert wave increased even further the count, now the memory-optimized object is using 37 MB of memory, an increase of 8 MB.

(Figure 5 – Second Insert Batch Operation Process.)

Now let’s suppose that the third batch insert operation will add more than 10.000 rows and the necessary memory will be beyond the target_memory_mb column that is the limit set for now (49 MB), what will happen with the insert block?  This is the real OOM [Out of Memory] scenario that will bel avoided by using the resource pool, SQL Server will throw an error during the process and will rollback the transaction instead.

The statement has been terminated.

Msg 701, Level 17, State 103, Line 184

There is insufficient system memory in resource pool ‘RG_MemoryOptimized’ to run this query.

The insert command required more than the target_memory_mb available at that moment (49 MB) to persist the data into the log file, the resource governor pool blocked the batch because the incoming request was out of the limit of the resource pool configuration. Adding this control layer will ensure that you will not stop in any OOM [Out Of Memory] problem or face any whack issue controlling your memory-objects in a database that have this two style of tables.

This gives you the control to manage and better administer the memory-optimized objects into your SQL Server instance. Now that the process failed due a lack of memory into the resource pool, you can share more memory and complete the operation just changing the resource pool parameters. In this case, I have added the MAX_MEMORY_PERCENT in 80%. Please have in mind that the 80% is related to the Max Server Memory set on the SQL Server Instance and nor of the OS.

</pre>
ALTER RESOURCE POOL RG_MemoryOptimized
WITH
(
MIN_MEMORY_PERCENT = 20,
MAX_MEMORY_PERCENT = 80
)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
SELECT pool_id
, Name
, min_memory_percent
, max_memory_percent
, max_memory_kb/1024 AS max_memory_mb
, used_memory_kb/1024 AS used_memory_mb
, target_memory_kb/1024 AS target_memory_mb
FROM sys.dm_resource_governor_resource_pools
<pre>

(Figure 6 – Adding Memory on RG_MemoryOptimized Pool.)

Now the resource pool has the sufficient memory (778 MB) to allocate and handle with a new insert operation.

</pre>
--4
;WITH DadosInsert AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CarrierTrackingNumber) AS ID, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,
RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate
FROM dbo.ondsk_VendasProdutos
--121.317
)
INSERT INTO inmem_VendasProdutos (IDSEQUENCE, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,
RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate)
SELECT *
FROM DadosInsert
WHERE ID BETWEEN 30001 AND 110000
(80000 row(s) affected)
<pre>

Link’s

https://technet.microsoft.com/en-us/library/bb934084(v=sql.105).aspx

https://msdn.microsoft.com/en-us/library/bb933866.aspx

https://msdn.microsoft.com/en-us/library/bb895145.aspx

https://msdn.microsoft.com/en-us/library/dn465874.aspx

https://msdn.microsoft.com/en-us/library/dn465873.aspx

Conclusion

Why is the Resource Governor vital for your environment when using memory-optimized objects? Because you don’t want to see, your memory being drained and capped when someone from the office calls to the DBA team because the performance of the SQL Server is under off the acceptable.

The Resource Pool option will bind the memory-optimized objects by adding a control layer that will prevent these tables to steal memory from the instance and the disk-based tables, they will live with the amount of memory that you will allow to have.