SQL SERVER – Resource Governor and Database Mapping

Recently in my trip to US for SQLPass, I had a unique opportunity to meet a number of attendees for my session. The session was well received and I got some great feedbacks. I loved the recent trip and it was a great experience for me to learn and prepare for the session. It was also an opportunity for me to learn from some of the deep conversations you folks brought during the session. If the session feedbacks and rating are any sense of how successful the session was, then I am glad to see they went really well. I look forward to many more sessions in the future too.

I met a number of you and you folks made even my lunch interesting. I thought I bring some conversations as blogs because these bring the simple nuances of SQL Server. I heard an interesting lunch time conversation when one of the attendees asked to how InMemory databases are using memory? Is there a way to find out which Resource Governor pool is using how much memory? In the past I wrote few simple blogs around these topics:

SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor

SQL SERVER – How to Bind Resource Governor for InMemory Enabled Databases?

I saw the usual answer from one of the individual that we have a number of DMVs that can help find this information. I thought of putting those DMVs here for quick reference:

-- Which resource pool is using how much of memory
SELECT pool_id
,NAME
,min_memory_percent
,max_memory_percent
,max_memory_kb / 1024 AS max_memory_in_MB
,used_memory_kb / 1024 AS used_memory_in_MB
,target_memory_kb / 1024 AS target_memory_in_MB
FROM sys.dm_resource_governor_resource_pools

 

Above DMV shows how the Resource Governor pools are configured and the values of memory currently. In case we have InMemory based databases, we need to understand which databases are configured to resource pools and what memory is currently mapped. This can also be got from the DMVs as shown below:

-- Check DB and pool binding
SELECT d.database_id
,d.NAME AS DbName
,d.resource_pool_id AS PoolId
,p.NAME AS PoolName
,p.min_memory_percent
,p.max_memory_percent
FROM sys.databases d
LEFT OUTER JOIN sys.resource_governor_resource_pools p
ON p.pool_id = d.resource_pool_id

The above shows the mapping of DB’s to resource pools. Do let me know if you have something similar used in your environments? If so, please share the same via comments.

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

SQL SERVER – How to Bind Resource Governor for InMemory Enabled Databases?

I have done a number of courses for Pluralsight. Last year when SQL Server 2014 was released, I planned to do a session on the same. The course SQL Server 2014 Administration New Features was a lot of hardwork but it did give me a chance to learn something new and share. Do let me know if you have every got a chance to listen to this course. Would love to hear your feedback.

As part of the course, one of the module included the basics of InMemory capabilities of SQL Server 2014. One of the listener from the course pinged me on Twitter with some interesting conversation. This inspired me to write this blog post.

Follower: Thanks for the InMemory basics, I had a simple doubt.

Pinal: Yes, please tell me. Let me see if I can answer the same.

Follower: I plan to upgrade my server to SQL 2014.

Pinal: Glad the course is helping you. Is there any doubt?

Follower: Yes, I might be planning to use the InMemory capabilities for couple of databases as part of application upgrade.

Pinal: Great.

Follower: I want to know can I restrict the amount of memory a particular InMemory OLTP Database can take.

Pinal: Can you please elaborate a bit please?

Follower: Simple Pinal, I want one DB not to take more than 20% of Memory on my server and the other InMemory DB not to take more than another 40% Memory available on the server.

Pinal: Interesting.

Follower: As part of upgrade I am consolidating and hence these restrictions can be of great help.

Pinal: Now I get it, it is possible with Resource Governor. Havent you tried it?

Follower: I think these are great pointers, I will dig it up. Thanks again.

Pinal: You are welcome. I will write it as a blog for sure in future.

This conversation has been in my minds for a while. It has taken some time to finally get this blog. The script one needs to write is simple. Let me walk you through the same.

  1. Create the Resource Pool
  2. Create the InMemory OLTP Filegroup enabled DB
  3. Bind the Resource Pool to our database
  4. Check the DB metadata
  5. Make the DB Offline and Online to make the changes take effect
  6. Clean up

As the script says, let us first create our Resource Group.

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

Next is to create the InMemory DB. This is same as used in the previous blog – Beginning In-Memory OLTP with Sample Example.

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

The next step is where the magic begins. We need to bind the DB and the resource pool. This can be achieved using the next command:

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

The success for this step can be viewed with this message:

A binding has been created. Take database ‘InMemory’ offline and then bring it back online to begin using resource pool ‘InMemory_Pool’

The next logical step is to check the metadata if the same has been mapped. Use the sys.databases DMV for this.

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

bind db to rg InMemory SQL SERVER   How to Bind Resource Governor for InMemory Enabled Databases?

Just like how resource governor configuration needs to be reconfigured. We need to do something similar for databases to make this changes take effect. We will need to take the DB offline and bring it online.

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

That is it. We are good now. Our InMemory DB will not take more than 40% of the memory allocated to SQL Server. Though this was a simple concept, I thought was worth a share. If you would like to clean up this experiment, please use the below script.

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

To learn such interesting SQL Server 2014 enhancements, feel free to listen to my Pluralsight course for more such topics.

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

SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor

Let us jump right away with question and answer mode.

What is resource governor?

Resource Governor is a feature which can manage SQL Server Workload and System Resource Consumption. We can limit the amount of CPU and memory consumption by limiting /governing /throttling on the SQL Server.

Why is resource governor required?

If there are different workloads running on SQL Server and each of the workload needs different resources or when workloads are competing for resources with each other and affecting the performance of the whole server resource governor is a very important task.

What will be the real world example of need of resource governor?

Here are two simple scenarios where the resource governor can be very useful.

Scenario 1: A server which is running OLTP workload and various resource intensive reports on the same server. The ideal situation is where there are two servers which are data synced with each other and one server runs OLTP transactions and the second server runs all the resource intensive reports. However, not everybody has the luxury to set up this kind of environment. In case of the situation where reports and OLTP transactions are running on the same server, limiting the resources to the reporting workload it can be ensured that OTLP’s critical transaction is not throttled.

Scenario 2: There are two DBAs in one organization. One DBA A runs critical queries for business and another DBA B is doing maintenance of the database. At any point in time the DBA A’s work should not be affected but at the same time DBA B should be allowed to work as well. The ideal situation is that when DBA B starts working he get some resources but he can’t get more than defined resources.

Does SQL Server have any default resource governor component?

Yes, SQL Server have two by default created resource governor component.

1) Internal –This is used by database engine exclusives and user have no control.

2) Default – This is used by all the workloads which are not assigned to any other group.

What are the major components of the resource governor?

  • Resource Pools
  • Workload Groups
  • Classification

In simple words here is what the process of resource governor is.

  • Create resource pool
  • Create a workload group
  • Create classification function based on the criteria specified
  • Enable Resource Governor with classification function

Let me further explain you the same with graphical image.

ResourceGovernor SQL SERVER   Simple Example to Configure Resource Governor   Introduction to Resource Governor

Is it possible to configure resource governor with T-SQL?

Yes, here is the code for it with explanation in between.

Step 0: Here we are assuming that there are separate login accounts for Reporting server and OLTP server.

/*-----------------------------------------------
Step 0: (Optional and for Demo Purpose)
Create Two User Logins
1) ReportUser, 2) PrimaryUser
Use ReportUser login for Reports workload
Use PrimaryUser login for OLTP workload
-----------------------------------------------*/

Step 1: Creating Resource Pool

We are creating two resource pools. 1) Report Server and 2) Primary OLTP Server. We are giving only a few resources to the Report Server Pool as described in the scenario 1 the other server is mission critical and not the report server.
-----------------------------------------------
-- Step 1: Create Resource Pool
-----------------------------------------------
-- Creating Resource Pool for Report Server
CREATE RESOURCE POOL ReportServerPool
WITH
( MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=30,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO
-- Creating Resource Pool for OLTP Primary Server
CREATE RESOURCE POOL PrimaryServerPool
WITH
( MIN_CPU_PERCENT=50,
MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50,
MAX_MEMORY_PERCENT=100)
GO

Step 2: Creating Workload Group

We are creating two workloads each mapping to each of the resource pool which we have just created.
-----------------------------------------------
-- Step 2: Create Workload Group
-----------------------------------------------
-- Creating Workload Group for Report Server
CREATE WORKLOAD GROUP ReportServerGroup
USING ReportServerPool
;
GO
-- Creating Workload Group for OLTP Primary Server
CREATE WORKLOAD GROUP PrimaryServerGroup
USING PrimaryServerPool
;
GO

Step 3: Creating user defined function which routes the workload to the appropriate workload group.

In this example we are checking SUSER_NAME() and making the decision of Workgroup selection. We can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER() etc.
-----------------------------------------------
-- Step 3: Create UDF to Route Workload Group
-----------------------------------------------
CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH
SCHEMABINDING
AS
BEGIN
DECLARE
@WorkloadGroup AS SYSNAME
IF
(SUSER_NAME() = 'ReportUser')
SET @WorkloadGroup = 'ReportServerGroup'
ELSE IF (SUSER_NAME() = 'PrimaryUser')
SET @WorkloadGroup = 'PrimaryServerGroup'
ELSE
SET
@WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO

Step 4: In this final step we enable the resource governor with the classifier function created in earlier step 3.
-----------------------------------------------
-- Step 4: Enable Resource Governer
-- with UDFClassifier
-----------------------------------------------
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Step 5: If you are following this demo and want to clean up your example, you should run following script. Running them will disable your resource governor as well delete all the objects created so far.
-----------------------------------------------
-- Step 5: Clean Up
-- Run only if you want to clean up everything
-----------------------------------------------
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.UDFClassifier
GO
DROP WORKLOAD GROUP ReportServerGroup
GO
DROP WORKLOAD GROUP PrimaryServerGroup
GO
DROP RESOURCE POOL ReportServerPool
GO
DROP RESOURCE POOL PrimaryServerPool
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

I hope this introductory example give enough light on the subject of Resource Governor. In future posts we will take this same example and learn a few more details.

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