Resource Governor and In-Memory OLTP

From BOL
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 to 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

USE [master]
GO
CREATE RESOURCE POOL [mem_xtp_pool] WITH(min_cpu_percent=0,
max_cpu_percent=100,
min_memory_percent=50,
max_memory_percent=60,
cap_cpu_percent=100,
AFFINITY SCHEDULER = AUTO
,min_iops_per_volume=0,
max_iops_per_volume=0)
GO

Bind the database to the resource pool.

EXEC sp_xtp_bind_db_resource_pool 'DatabaseName', 'mem_xtp_pool' GO

Confirm the binding. The resource pool id for the database should be NULL.

SELECT d.database_id, d.name, d.resource_pool_id
FROM sys.databases d
GO

Make the binding effect. Take the database offline and back online after binding it to the resource pool

ALTER DATABASE DatabaseName SET OFFLINE
GO
ALTER DATABASE DatabaseName SET ONLINE

Leave a Reply

Your email address will not be published. Required fields are marked *