SQL Server 2014 introduced the InMemory OLTP feature, which allows for faster data processing by storing tables in memory. This feature can be particularly useful for applications that require high performance and low latency. However, it is important to manage the memory usage of these InMemory databases to ensure optimal performance for other databases on the server.

In this blog post, we will discuss how to restrict the amount of memory a particular InMemory OLTP database can take in SQL Server 2014.

The first step is to create a resource pool that will limit the memory usage for the InMemory database. This can be done using the following script:

-- Step 1: Create the Resource Pool. Limit to 40% memory.
CREATE RESOURCE POOL InMemory_Pool WITH ( MAX_MEMORY_PERCENT = 40 );
ALTER RESOURCE GOVERNOR RECONFIGURE ;

Next, we need to create the InMemory database and bind it to the resource pool. This can be achieved using the following script:

-- Step 2: Create the InMemory DB
CREATE DATABASE InMemory ON PRIMARY (
    NAME = InMemoryData ,
    FILENAME = 'd:\data\InMemoryData.mdf' ,
    size = 100MB
), -- Memory Optimized Data
FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA (
    NAME = [InMemory_InMem_dir] ,
    FILENAME = 'd:\data\InMemory_InMem_dir'
)
LOG ON (
    name = [InMem_demo_log] ,
    Filename = 'd:\data\InMemory.ldf' ,
    size = 50MB
)
GO

-- Step 3: Bind the resource pool to DB
EXEC sp_xtp_bind_db_resource_pool 'InMemory' , 'InMemory_Pool'

After binding the database to the resource pool, we can check the metadata to ensure that the mapping has been successful:

-- Step 4: Check the Database metadata
SELECT dbs.database_id , dbs.name , dbs.resource_pool_id
FROM sys.databases dbs
WHERE name LIKE 'InMemory'

Once the metadata has been verified, we need to take the InMemory database offline and bring it back online for the resource pool changes to take effect:

-- Step 5: For RG to take effect, make DB Offline and Online
USE MASTER
GO
ALTER DATABASE InMemory SET OFFLINE
GO
ALTER DATABASE InMemory SET ONLINE
GO

With these steps, we have successfully restricted the memory usage of the InMemory OLTP database to 40% of the total memory allocated to SQL Server.

If you wish to clean up this experiment, you can use the following script:

-- Clean up
USE MASTER
GO
DROP DATABASE InMemory
GO
DROP RESOURCE POOL InMemory_Pool
ALTER RESOURCE GOVERNOR RECONFIGURE ;
GO

Managing memory usage for InMemory OLTP databases is crucial for maintaining optimal performance in SQL Server. By following these steps, you can ensure that the memory allocation is controlled and other databases on the server are not affected.

For more information on SQL Server 2014 enhancements and other interesting topics, you can listen to my Pluralsight course.

Reference: Pinal Dave (https://blog.sqlauthority.com)