Bind a Database with Memory-Optimized Tables to a Resource Pool
A resource pool represents a subset of physical resources that can be governed. By default, SQL Server databases are bound to and consume the resources of the default resource pool. To protect SQL Server from having its resources consumed by one or more memory-optimized tables, and to prevent other memory users from consuming memory needed by memory-optimized tables, you should create a separate resource pool to manage memory consumption for the database with memory-optimized tables.
A database can be bound on only one resource pool. However, you can bind multiple databases to the same pool. SQL Server allows binding a database without memory-optimized tables to a resource pool but it has no effect. You may want to bind a database to a named resource pool if, in future, you may want to create memory-optimized tables in the database.
Before you can bind a database to a resource pool both the database and the resource pool must exist. The binding takes effect the next time the database is brought online. See Database States for more information.
For information about resource pools, see Resource Governor Resource Pool.
Create the database and resource pool
You can create the database and resource pool in any order. What matters is that they both exist prior to binding the database to the resource pool.
Create the database
The following Transact-SQL creates a database named IMOLTP_DB which will contain one or more memory-optimized tables. The path <driveAndPath> must exist prior to running this command.
CREATE DATABASE IMOLTP_DB GO ALTER DATABASE IMOLTP_DB ADD FILEGROUP IMOLTP_DB_fg CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE IMOLTP_DB ADD FILE( NAME = 'IMOLTP_DB_fg' , FILENAME = 'c:\data\IMOLTP_DB_fg') TO FILEGROUP IMOLTP_DB_fg; GO
Determine the minimum value for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT
Once you determine the memory needs for your memory-optimized tables, you need to determine what percentage of available memory you need, and set the memory percentages to that value or higher.
For this example we will assume that from your calculations you determined that your memory-optimized tables and indexes need 16 GB of memory. Assume that you have 32 GB of memory committed for your use.
At first glance it could seem that you need to set MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT to 50 (16 is 50% of 32). However, that would not give your memory-optimized tables sufficient memory. Looking at the table below (Percent of memory available for memory-optimized tables and indexes) we see that if there is 32 GB of committed memory, only 80% of that is available for memory-optimized tables and indexes. Therefore, we calculate the min and max percentages based upon the available memory, not the committed memory.
memoryNeedeed = 16
memoryCommitted = 32
availablePercent = 0.8
memoryAvailable = memoryCommitted * availablePercent
percentNeeded = memoryNeeded / memoryAvailable
Plugging in real numbes:
percentNeeded = 16 / (32 * 0.8) = 16 / 25.6 = 0.625
Thus you need at least 62.5% of the available memory to meet the 16 GB requirement of your memory-optimized tables and indexes. Since the values for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT must be integers, we set them to at least 63%.
Create a resource pool and configure memory
When configuring memory for memory-optimized tables, the capacity planning should be done based on MIN_MEMORY_PERCENT, not on MAX_MEMORY_PERCENT. See ALTER RESOURCE POOL (Transact-SQL) for information on MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT. This provides more predictable memory availability for memory-optimized tables as MIN_MEMORY_PERCENT causes memory pressure to other resource pools to make sure it is honored. To ensure that memory is available and help avoid out-of-memory conditions, the values for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT should be the same. See Percent of memory available for memory-optimized tables and indexes below for the percent of memory available for memory-optimized tables based on the amount of committed memory.
See Best Practices: Using In-Memory OLTP in a VM environment for more information when working in a VM environment.
The following Transact-SQL code creates a resource pool named Pool_IMOLTP with half of the memory available for its use. After the pool is created Resource Governor is reconfigured to include Pool_IMOLTP.
-- set MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT to the same value CREATE RESOURCE POOL Pool_IMOLTP WITH ( MIN_MEMORY_PERCENT = 63, MAX_MEMORY_PERCENT = 63 ); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO
Bind the database to the pool
Use the system function
sp_xtp_bind_db_resource_pool to bind the database to the resource pool. The function takes two parameters: the database name and the resource pool name.
The following Transact-SQL defines a binding of the database IMOLTP_DB to the resource pool Pool_IMOLTP. The binding does not become effective until you bring the database online.
EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'Pool_IMOLTP' GO
The system function sp_xtp_bind_db_resourece_pool takes two string parameters: database_name and pool_name.
Confirm the binding
Confirm the binding, noting the resource pool id for IMOLTP_DB. It should not be NULL.
SELECT d.database_id, d.name, d.resource_pool_id FROM sys.databases d GO
Make the binding effective
You must take the database offline and back online after binding it to the resource pool for binding to take effect. If your database was bound to an a different pool earlier, this removes the allocated memory from the previous resource pool and memory allocations for your memory-optimized table and indexes will now come from the resource pool newly bound with the database.
USE master GO ALTER DATABASE IMOLTP_DB SET OFFLINE GO ALTER DATABASE IMOLTP_DB SET ONLINE GO USE IMOLTP_DB GO
And now, the database is bound to the resource pool.
Change MIN MEMORY PERCENT and MAX MEMORY PERCENT on an existing pool
If you add additional memory to the server or the amount of memory needed for your memory-optimized tables changes, you may need to alter the value of MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT. The following steps show you how to alter the value of MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT on a resource pool. See the section below, for guidance on what values to use for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT. See the topic Best Practices: Using In-Memory OLTP in a VM environment for more information.
ALTER RESOURCE POOLto change the value of both MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT.
ALTER RESURCE GOVERNORto reconfigure the Resource Governor with the new values.
ALTER RESOURCE POOL Pool_IMOLTP WITH ( MIN_MEMORY_PERCENT = 70, MAX_MEMORY_PERCENT = 70 ) GO -- reconfigure the Resource Governor ALTER RESOURCE GOVERNOR RECONFIGURE GO
Percent of memory available for memory-optimized tables and indexes
If you map a database with memory-optimized tables and a SQL Server workload to the same resource pool, the Resource Governor sets an internal threshold for In-Memory OLTP use so that the users of the pool do not have conflicts over pool usage. Generally speaking, the threshold for In-Memory OLTP use is about 80% of the pool. The following table shows actual thresholds for various memory sizes.
When you create a dedicated resource pool for the In-Memory OLTP database, you need to estimate how much physical memory you need for the in-memory tables after accounting for row versions and data growth. Once estimate the memory needed, you create a resource pool with a percent of the commit target memory for SQL Instance as reflected by column 'committed_target_kb' in the DMV
sys.dm_os_sys_info (see sys.dm_os_sys_info). For example, you can create a resource pool P1 with 40% of the total memory available to the instance. Out of this 40%, the In-Memory OLTP engine gets a smaller percent to store In-Memory OLTP data. This is done to make sure In-Memory OLTP does not consume all the memory from this pool. This value of the smaller percent depends upon the Target committed Memory. The following table describes memory available to In-Memory OLTP database in a resource pool (named or default) before an OOM error is raised.
|Target Committed Memory||Percent available for in-memory tables|
|<= 8 GB||70%|
|<= 16 GB||75%|
|<= 32 GB||80%|
|<= 96 GB||85%|
For example, if your 'target committed memory' is 100 GB, and you estimate your memory-optimized tables and indexes need 60GBof memory, then you can create a resource pool with MAX_MEMORY_PERCENT = 67 (60GB needed / 0.90 = 66.667GB - round up to 67GB; 67GB / 100GB installed = 67%) to ensure that your In-Memory OLTP objects have the 60GB they need.
Once a database has been bound to a named resource pool, use the following query to see memory allocations across different resource pools.
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
This sample output shows that the memory taken by memory-optimized objects is 1356 MB in resource pool, PoolIMOLTP, with an upper bound of 2307 MB. This upper bound controls the total memory that can be taken by user and system memory-optimized objects mapped to this pool.
This output is from the database and tables we created above.
pool_id Name min_memory_percent max_memory_percent max_memory_mb used_memory_mb target_memory_mb ----------- ----------- ------------------ ------------------ ------------- -------------- ---------------- 1 internal 0 100 3845 125 3845 2 default 0 100 3845 32 3845 259 PoolIMOLTP 0 100 3845 1356 2307
For more information see sys.dm_resource_governor_resource_pools (Transact-SQL).
If you do not bind your database to a named resource pool, it is bound to the 'default' pool. Since default resource pool is used by SQL Server for most other allocations, you will not be able to monitor memory consumed by memory-optimized tables using the DMV sys.dm_resource_governor_resource_pools accurately for the database of interest.
Resource Governor Resource Pool
Create a Resource Pool
Change Resource Pool Settings
Delete a Resource Pool