How To Limit a User’s Resource Usage Using Resource Governor

dbtut March 15, 2019 MSSQL

Resource Governor announced with SQL Server 2008. We are able to limit resources of a session using Resource Governor. For example, a report user is executing report queries and consume entire memory and cpu in the operating system. Therefore, memory and CPU bottleneck cause slowness in other applications. In such a case, we can use Resource Governor to limit the resources that the report user uses.

We could limit only memory and CPU usage until SQL Server 2014. We couldn’t limit the I/O. We can also limit IO using Max and Min IOPS since SQL Server 2014.

There are 3 concepts you need to understand in Resource Governor:

Resource Pool

Represents the resources of the server. You can think of a virtual instance in the instance. By default, 2 resource pools are created. (Internal and default)

When we use resource Governor to limit users, we create our own resource pools to limit. In fact, we limit these resource pools, not users. In the following example, the maximum CPU ratio that resource pool can use is limited to 20%.

CREATE RESOURCE POOL [ReportPool] 
WITH(
     MAX_CPU_PERCENT=20, 
    )

CREATE RESOURCE POOL [ReportPool]

WITH(

MAX_CPU_PERCENT=20,

)

internal pool: The pool that SQL Server uses for its internal work.

default pool: This is the first user defined pool in the resource governor. If the Resource Governor enabled, Undirected connections to the “user defined resource pool” come to this pool.

Workload Group

You can think of this as a container that can be used to redirect queries with the same criteria to the related resource pool. One or more workload groups can be redirected to 1 resource pool.

Classification

With Classifier Function, requests sent by sessions are checked and directed to the specified workload group.

To better understand Resource Governor, let’s make an example about cpu, memory and disk limitations.

First, we activate Resource Governor through SSMS as follows.

We then create the resource pool to be limited, using the following query. We will make sure that the queries from this resource pool do not exceed 20% of the maximum CPU , 30% of the maximum memory, and 1000 of the IOPS per volume.

With the CAP_CPU_PERCENT parameter, we say that it can use up to 80% of the maximum, even with cpu idle.

What is the difference between CAP_CPU_PERCENT MAX_CPU_PERCENT?

When we set the MAX_CPU_PERCENT parameter for a resource pool, if the cpu is idle, resource pool could exceed the max cpu limit reserved for it and it could use the whole CPU. Therefore, there could be short-term problems when other applications or other queries in the operating system needed CPU.

When we set the CAP_CPU_PERCENT parameter, we set the maximum CPU that the resource pool can use, even if it is cpu idle. In this way, other applications or queries other than the resource pool do not experience this problem.

In the example, with the AFFINITY SCHEDULER parameter, we specify that the resource pool will use schedulers eighth, and from 12 to 16.

CREATE RESOURCE POOL [ReportPool] 
WITH(
		MAX_CPU_PERCENT=20, 
		MAX_MEMORY_PERCENT=30,
		CAP_CPU_PERCENT = 80, 
		MAX_IOPS_PER_VOLUME=1000,
		AFFINITY SCHEDULER = (8, 12 TO 16)
	)

CREATE RESOURCE POOL [ReportPool]

WITH(

MAX_CPU_PERCENT=20,

MAX_MEMORY_PERCENT=30,

CAP_CPU_PERCENT = 80,

MAX_IOPS_PER_VOLUME=1000,

AFFINITY SCHEDULER = (8, 12 TO 16)

)

We create the related workload group using the following script.

CREATE WORKLOAD GROUP [ReportGroup] 
USING [ReportPool]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

CREATE WORKLOAD GROUP [ReportGroup]

USING [ReportPool]

ALTER RESOURCE GOVERNOR RECONFIGURE;

Then we need to create the classifier function that will redirect the report related queries to the related workload group. In the following example, we redirect the following users to the ReportGroup workload group.

If the user name is ReportUser
If the application name is “MyApplication”
If the host name is “MyServer”

CREATE FUNCTION Class_Func() RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
  DECLARE @workload_group sysname;
 
  IF ( SUSER_SNAME() = 'ReportUser')
      SET @workload_group = 'ReportGroup';
  ELSE IF (APP_NAME() = 'MyApplication')
	  SET @workload_group = 'ReportGroup';
  ELSE IF (HOST_NAME() = 'MyServer')
	  SET @workload_group = 'ReportGroup';
  RETURN @workload_group;
END;

CREATE FUNCTION Class_Func() RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

DECLARE @workload_group sysname;

IF ( SUSER_SNAME() = 'ReportUser')

SET @workload_group = 'ReportGroup';

ELSE IF (APP_NAME() = 'MyApplication')

SET @workload_group = 'ReportGroup';

ELSE IF (HOST_NAME() = 'MyServer')

SET @workload_group = 'ReportGroup';

RETURN @workload_group;

END;

Finally, we are running the following script for the resource governor to work with the Class_Func function we created above.

USE master
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Class_Func);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

USE master

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Class_Func);

ALTER RESOURCE GOVERNOR RECONFIGURE;

If two workload groups shares a resource pool , you can assign a priority to one of the workload groups as follows.

ALTER WORKLOAD GROUP ReportGroup
WITH (IMPORTANCE = High)

1 2	ALTER WORKLOAD GROUP ReportGroup WITH (IMPORTANCE = High)

You can configuru other settings for Workload Groups as follows.

ALTER WORKLOAD GROUP ReportGroup
WITH 
(
--Determines the maximum percentage of memory that a single query can use in the pool. 
REQUEST_MAX_MEMORY_GRANT_PERCENT=20, 
--The information that the CPU threshold is exceeded is triggered after 5 seconds. The query is not stopped. 
REQUEST_MAX_CPU_TIME_SEC = 5,
--Specifies the maximum wait time for a query to take memory. 
--In this example, if the query does not take memory for 5 seconds, it will be timeout. 
REQUEST_MEMORY_GRANT_TIMEOUT_SEC=5,
--Specifies the maxdop setting that a query can run.
MAX_DOP = 1, 
--Limits the number of queries that can run concurrently in the workload group. 
--In this example, maximum of 100 queries can run in this workload group at the same time.
GROUP_MAX_REQUESTS = 100
)

ALTER WORKLOAD GROUP ReportGroup

WITH

(

--Determines the maximum percentage of memory that a single query can use in the pool.

REQUEST_MAX_MEMORY_GRANT_PERCENT=20,

--The information that the CPU threshold is exceeded is triggered after 5 seconds. The query is not stopped.

REQUEST_MAX_CPU_TIME_SEC = 5,

--Specifies the maximum wait time for a query to take memory.

--In this example, if the query does not take memory for 5 seconds, it will be timeout.

REQUEST_MEMORY_GRANT_TIMEOUT_SEC=5,

--Specifies the maxdop setting that a query can run.

MAX_DOP = 1,

--Limits the number of queries that can run concurrently in the workload group.

--In this example, maximum of 100 queries can run in this workload group at the same time.

GROUP_MAX_REQUESTS = 100

)

You can see the sessions in the worklad groups using the following query.

SELECT s.session_id,s.login_name, g.name
FROM sys.dm_exec_sessions AS s  
INNER JOIN sys.dm_resource_governor_workload_groups AS g  
    ON g.group_id = s.group_id  
	where session_id>60
ORDER BY g.name;  
GO

SELECT s.session_id,s.login_name, g.name

FROM sys.dm_exec_sessions AS s

INNER JOIN sys.dm_resource_governor_workload_groups AS g

ON g.group_id = s.group_id

where session_id>60

ORDER BY g.name;

You can see the queries in the worklad groups using the following query.

SELECT sder.group_id, wg.name, sder.status, sder.session_id, sder.request_id, 
    sder.start_time, sder.command, sder.sql_handle, st.text   
FROM sys.dm_exec_requests AS sder  
INNER JOIN sys.dm_resource_governor_workload_groups AS wg  
    ON wg.group_id = sder.group_id  
CROSS APPLY sys.dm_exec_sql_text(sder.sql_handle) AS st  
ORDER BY wg.name;

SELECT sder.group_id, wg.name, sder.status, sder.session_id, sder.request_id,

sder.start_time, sder.command, sder.sql_handle, st.text

FROM sys.dm_exec_requests AS sder

INNER JOIN sys.dm_resource_governor_workload_groups AS wg

ON wg.group_id = sder.group_id

CROSS APPLY sys.dm_exec_sql_text(sder.sql_handle) AS st

ORDER BY wg.name;

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

How To Limit a User’s Resource Usage Using Resource Governor

There are 3 concepts you need to understand in Resource Governor:

Resource Pool

Workload Group

Classification

About dbtut

Leave a Reply Cancel reply