SQL SERVER – Concept, Benefits and Use of Resource Governor in SQL Server

Concept Resource Governor in SQL Server

Benefits of Resource Governor in SQL Server 

Use of Resource Governor in SQL Server

Example of Resource Governor in SQL Server 

Resource POOL in Resource Governor in SQL Server  

Classification Resource Governor in SQL Server 

Workload Group Resource Governor in SQL Server   

How to Drop  Resource Governor in SQL Server   

How to  Disable Resource Governor in SQL Server

How to reset statistic Resource Governor in SQL Server   

Resource Governor in SQL Server  

Error: Cannot drop user-defined function dbo.ResourceClassifier. It is being used as a resource governor classifier.

 

SQL server 2008 has provided this feature to mange and workload of resources of SQL Server instance and system database.  Resource Governor can manage the resource utility for SQL Server and using it we can specify the memory and CUP limits for requesting application or task. It is available in SSMS, Object explorer -> Management tab.

There are only 3 concepts in Resource Governor in SQL Server

Resource POOL: When we install new SQL Server it’s create 2 Resource Pool that is Internal and Default. We can also add User Defined Resource Pool.

Workload Group: When we install new SQL Server it’s create 2 Resource Workload Group that is Internal and Default. We can also add User Defined Resource Workload Group. Work group are mapped with respective Resource Pool.

Classification: It is support is user defined function to classifier. Its work is to classify of incoming request and decides the way of internal rule.

Example:  I have to handle the work load of named MYPOOL application and this application work as Data mining which perform operation on huge databases. So now we can handle to define the limit of the resource and utilization of SQL Server.

Step 1: To Create POOL, Workload Group

--Resource pool to be used by named MyPool Application

CREATE RESOURCE POOL My_Pool

WITH

(

 MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,

 MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100

)

GO

--create New resource pool to be used by named Reporting Application

CREATE RESOURCE POOL My_Report_Pool

WITH

(

 MIN_CPU_PERCENT=25, MAX_CPU_PERCENT=100,

 MIN_MEMORY_PERCENT=25, MAX_MEMORY_PERCENT=100

)

GO

--Workload Group to be used by named MyPool Application

CREATE WORKLOAD GROUP My_Group

    USING My_Pool ;

GO

--Workload Group to be used by named Reporting Application

CREATE WORKLOAD GROUP My_Report_Group

    USING My_Report_Pool ;

GO   

Step 2: To create User-Defined Function for User.

USE master;

GO

CREATE FUNCTION dbo.ResourceClassifier()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

 --Declare the variable to hold the value returned in sysname.

 DECLARE @My_Workload_Group AS SYSNAME

 --If the user login is 'bulk_admin', than only map the connection to ---the My_Group Group workload group.

 IF (SUSER_NAME() = 'bulk_admin')

  SET @My_Workload_Group = 'My_Group'

 --If the user login is 'sa',than only map the connection to

 --the My_Report_Group workload group.

 ELSE IF (SUSER_NAME() = 'sa')

  SET @My_Workload_Group = 'My_Report_Group'

 ELSE

  SET @My_Workload_Group = 'default'

 RETURN @My_Workload_Group

END

GO

Step 3: To register created function and reconfigure, Disable and Resetting the statistics of Resource governor.

--Now we have to register the classifier for user-defined function and -- update the in memory configuration.

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION=dbo.ResourceClassifier);

GO

--Enabling Resource Governor(By default when you install

--SQL Server, Resource Governor is disabled)

--It loads the stored configuration metadata into memory

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

--To disabling whole Resource Governor

ALTER RESOURCE GOVERNOR DISABLE

GO

--The below T-SQL will used as resets statistics on all workload groups and resource pools.

ALTER RESOURCE GOVERNOR RESET STATISTICS

GO

Step 4: All data clean if it is not required

--NOTE- Clean all data after testing this example

Drop RESOURCE POOL My_Pool

drop WORKLOAD GROUP My_Group

DROP WORKLOAD GROUP My_Report_Group

DROP RESOURCE POOL My_Report_Pool

ALTER RESOURCE GOVERNOR WITH  (CLASSIFIER_FUNCTION=null)

DROP FUNCTION dbo.ResourceClassifier

--NOTE- if you will direct use the DROP FUNCTION <FUNCTION NAME> Commnad then it will return the following error so you have to use below commnad to resolve it.

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function dbo.ResourceClassifier. It is being used as a resource governor classifier.

ALTER RESOURCE GOVERNOR WITH  (CLASSIFIER_FUNCTION=null)

DROP FUNCTION dbo.ResourceClassifier

Step 5: The Following snap shot shown the create POOL, Workload Group and Classification:-

This above example can be done using also Window wizards