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

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?

Solarwinds

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.

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

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 (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Fix: Error 147 An aggregate may not appear in the WHERE clause
Next Post
SQL SERVER – Fix: Error: 10920 Cannot drop user-defined function. It is being used as a resource governor classifier

Related Posts

40 Comments. Leave new

  • As ever, Pinal Dave leads us the way in what would otherwise a substantial hurdle in configuring Sql server. Sadly however there is a substantial error in this article: the classification function is NOT triggered by a Request, it is triggered by opening a Session, as is stated here:
    This means that have close and open a session every time you reconfigure the Resource Governor.

    (I also hope this information might be helpful to Pruthvi Raj and Binny Mathew.)
    If you keep this in mind this article is great introduction.

    Reply
  • Hello Pinal, our applications have got a lot of adhoc job requests which needs to be run at any given point in time. Also there is a webinterface designed in our system, but whenever the jobs are running in the background, the webinterface slows down (inserts, updates, searches) , which results in poor user experience.

    So i need to now define a solution wherein whenever these adhoc jobs are being executed, it doesnt eat up all the resources. Also whenever a user tries using this webinterface during the ongoing loads, his request should be prioritized and be given all the needed resource (because it would hardly last for 5-10 seconds per request).

    Can i address this problem using resource governor? If yes then how? Please guide me out here.

    Reply
    • First you need to figure out the bottleneck. From the face of the problem its looks like blocking issue. what are the waits you are seeing during problem time?

      Reply
  • Thanks for a great introductory example Pinal!

    Is there any way to “test” if everything is set up correctly and that my user-login is being assigned to the correct workload group? I tried googling but haven’t found anything… Thanks in advance

    Reply
  • Eric Maynard
    June 8, 2016 12:49 am

    Alex,

    You can verify that requests are being classified as expected. Look at the example queries under the heading “To verify the resource pools, workload groups, and the classifier user-defined function” here: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/create-and-test-a-classifier-user-defined-function?view=sql-server-2014

    Reply
  • You can check your classifications are working by looking at SQL Activity Monitor | Processes.
    You will see the under the column ‘Workload Group’ the different classifications for your sessions.

    Reply
  • Finally. A practical example of something! Very good!

    Reply
  • very good article
    unfortunately I cant get it to work , the workgroup is not being use and I cant find the issue no errors reported- must be a basic reason for this .
    . also should suser name be st-network\oliverk-admin or just oliverk-admin for example

    Reply
  • why cant i unsubscribe to your emails ? when i click unsubscribe all i get is a blank screen in IE

    Reply
  • Can we Create multiple resourcw pool for single workload server group

    Reply
  • Jan Michael Nuqui
    May 30, 2017 12:35 pm

    Do we need to restart SQL Server to setup Resource Governor?

    Reply
  • Hi Pinal,

    Can u guide me how I can find queries causing I/O bottlenecks without using SQLSERVER PROFILER or any other third party tools.

    Can u help me with the link of such Article.

    Reply
  • Can we add AD group in Resource governor instead of single user

    Reply
  • Thank you for the example. I have an issue in my current server where one of the remote program which runs the DML statements continuously and eating up most of the resources of SQL and slowing down rest of the operations. I see the program is creating multiple sessions and eating up resources and most of the times blocking. So I changed DOP to 1 to limit creating multiple sessions by this program however this limited the resource usage but has lead to under-utilization of server CPU (more than 10 CPU’s) resources. Can I configure DOP as well using the Resource governor? so that I can leave DOP to 0 from Server settings and configure to use limited DoP’s for requests from a particular user.

    Reply
  • I am having issue running heavy ad hoc query/job cause users can not access our app.

    I tried this but “Only Enterprise edition of SQL Server supports resource governor”. And I am using SQL Server Pro.

    It did say “You can manipulate resource governor metadata” so I am thinking just change the default to limit the CPU usage to 90% (instead of the 100%). Not sure how I do that. And even if can, will it work in my situation.

    Thanks

    John

    Reply

Leave a Reply

Menu