Closure
Closure
Home » Databases » Microsoft SQL Server » How do we limit the resources that a user can consume in SQL Server

How do we limit the resources that a user can consume in SQL Server

How do we limit the resources that a user can consume in SQL Server
, , , , Leave a comment
How do we limit the resources that a user can consume in SQL Server
Latest posts by Stratos Matzouranis (see all)

Many times a user in the database can consume all the resources that the SQL Server instance has and no other work can be done by another user. In this article we will see how we can limit it by using it Resource Governor.

THE SQL Server Resource Governor is a SQL Server tool that can define per user group resource consumption based on workload. Through the Resource Governor we can set limits on CPU, I/O, and memory usage.

What does the Resource Governor consist of?

Its configuration consists of 3 basic steps:

  • ResourcePool: Which is a group of resources in which we define the limits that it will be able to use. During its creation we have the possibility to define the parameters:
    • MIN_CPU_PERCENT / MAX_CPU_PERCENT: The lower / upper limit that this Pool can consume CPU in relation to the rest.
    • CAP_CPU_PERCENT: When it does not request resources from another Pool even though we have set a MAX_CPU_PERCENT limit then it can exceed the percentage we have set. With this parameter we set the hard limit which it can use for CPU.
    • MIN_MEMORY_PERCENT / MAX_MEMORY_PERCENT: The lower / upper limit that this Pool can consume memory in relation to the rest
    • Affinity: With this parameter we can define a specific CPU core to use this Pool.
    • MIN_IOPS_PER_VOLUME / MAX_IOPS_PER_VOLUME: The lower / upper limit of Input/Output operations per second per disk that this Pool can consume.
  • Workload Group: It is the group with the users we want to restrict. Each group is connected to one Resource Pool.
  • Classification: Creating one Classification Function we can define in which Workload Group belongs to each incoming connection from a user.

The example

Let's see an example of enabling Resource Governor step by step.

First, activate it with the following command:

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

We create it Resource Pool with the limits we want it to have:

CREATE RESOURCE POOL Limited
WITH (MAX_CPU_PERCENT = 60 , CAP_CPU_PERCENT = 90, MAX_MEMORY_PERCENT = 70 , MAX_IOPS_PER_VOLUME = 7168);

We create it Workload Group assigning him to use the Resource Pool which we made in the previous step:

CREATE WORKLOAD GROUP [ApplicationGroup]
USING Limited;
GO

Then we create it Classification Function which will define according to the user who is going to connect if it will belong to someone Workload Group:

USE master
GO

CREATE FUNCTION dbo.Classifier() 
RETURNS sysname WITH SCHEMABINDING
as
BEGIN
	DECLARE @group_name sysname

	IF (SUSER_NAME() = 'app_user')
		SET @group_name = 'ApplicationGroup'
 
	RETURN @group_name;
END
GO

Finally, we set the Resource Governor to make use of the Classification Function we made in the previous step:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Classifier);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

After completing all the steps we will see through the SQL Server Management Studio that to Object Explorer under the folder Management, will have created the Resource Pool with all the parameters we set.

How do we limit the resources that a user can consume in SQL Server

With right click properties on the Resource Governor we can see graphically the parameters we set.

How do we limit the resources that a user can consume in SQL Server

Now every time that user logs in, they will is subject to to the limitations those we have set.

Sources:

Share it

Leave a reply