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

 

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

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.

We create the related workload group using the following script.

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”
Finally, we are running the following script for the resource governor to work with the Class_Func function we created above.
If two workload groups shares a resource pool , you can assign a priority to one of the workload groups as follows.
You can configuru other settings for Workload Groups as follows.
You can see the sessions in the worklad groups using the following query.
You can see the queries in the worklad groups using the following query.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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