What is RG ?

Resource Governor (RG) is a mechanism by that specific resources are allocated to resource groups, where we can define groups based on applications or users and therefore limit how much resources are used.

Think a situation, where application and reporting use same database server.Request are coming from application and report end.For executing analytical reports (high concurrent users presence ) then significant amount of resource can be consumed by report processing .Application users feels transaction execution slowness.

Another situation, multiple application usages same database server then using RG you govern on the physical resources. Assign specify resources(Memory,CPU,IO) for individual application.

RG03

How it works ?

RG in SQL Server which enable us to control physical Resources means CPU , memory and IO usage.We can classify request , session coming from application A to a group Workload group A.Next step we’ll configure resource pool A and it run the workload A defined earlier.After that resource can be assigned to the resource pool say memory 30% and CPU 50%. Steps are as below :

  1. Enable SQL Server Resource Governor
  2. Create Resource Governor Workload Group
  3. Create Resource Governor Classifier Function
  4. Assign Resource Governor Classifier Function to Use
  5. Define Resource Governor Pool Settings
  6. Testing SQL Server Resource Governor Settings

Configuring Resource Governor:

SSMS, go to Management > Resource Governor, right click and select New Resource Pool.Manage Resource Governor settings by right clicking on Resource Governor and selecting Properties and you will get a screen like below where you can set the values:

RG01

Limitation :

  1. It work on user sessions only not the system sessions.
  2. It will works data read operation from data files in a OLTP system but not writing data to log files.
  3. Resource Governor can only be applied to SQL Server database engine services. You cannot enable Resource Governor for SQL Server Analysis Services (SSAS), SQL Server Integration Service (SSIS) or SQL Server Reporting Services (SSRS).
  4. Resource Governor is available only with Enterprise Edition and Developer Edition. Since Enterprise Edition is costly, unfortunately, you need to pay more money to use this feature

Where is it implementable :

  1. Not all resources can be governed by the Resource Governor. IO, CPU, Memory, Degree of Parallelism are the resources you can control from Resource Governor.
  2. Resource Governor (RG) supports SQL Server and Azure SQL Database (Managed Instance). It does not support Azure SQL Data Warehouse and Parallel Data Warehouse.
  3. RG may create balanced situation (of resources )where multiple application usage same database.