Home > In-MemoryOLTP > Resource Governor and In-Memory OLTP

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 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

 

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 database should be NULL.

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

 

Make the binding effective

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

Categories: In-MemoryOLTP
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: