Introduction



I want to use resource governor which is an enterprise edition feature to control the amounts of CPU that my maintenance tasks use. My maintenance tasks include such activities as index rebuilds, DBCC CHECKDB and backups where compression has been enabled.

 Therefore I decided to create two pools, a so-called slow one called CPUSLOW and a fast one called CPUFAST. The CPUSLOW pool is assigned a maximum of 10% of CPU during contention periods and the fast pool I had assigned 90%.

The key element to my solution here is that The MAX_CPU_PERCENT value tells the scheduler how to allocate resources when there is contention between workloads.

 

Code

USE master
GO
 
-- Create Pools
 
CREATE RESOURCE POOL CPUSLOW
WITH
 
(  max_cpu_percent = 10)
 
CREATE RESOURCE POOL CPUFAST
WITH
 
 ( max_cpu_percent = 90)
 
 GO
 
-- Create groups
 
 CREATE WORKLOAD GROUP SQLCPUFAST
USING CPUFAST;
GO
 
CREATE WORKLOAD GROUP SQLCPUSLOW
USING CPUSLOW;
GO

For classification to work we need to separate the incoming connections by a specific login, so from the below I bind my incoming maintenance tasks to a login called SQLCheck which is aligned to the SQLCPUSLOW workload group.

-- My classifier
 
IF OBJECT_ID('dbo.checkme','FN') IS NOT NULL
DROP FUNCTION dbo.checkme
 
USE master
GO
CREATE FUNCTION dbo.checkme()
RETURNS SYSNAME WITH SCHEMABINDING
 
AS
 
BEGIN
 
DECLARE @val sysname
IF 'SQLCheck' = SUSER_SNAME()
SET @val = 'SQLCPUSLOW';
 
            ELSE IF 'DOMAIN\SQLSERVERSERVICE' = SUSER_SNAME()
                      SET @val = 'SQLCPUFAST';
 
RETURN @val;
END
 
GO
 
  
 
-- Now enable
 
USE master
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.checkme);
 
ALTER RESOURCE GOVERNOR RECONFIGURE

Run the following code to check if classification is correct. 

-- checks connections
 
USE master
GO
 
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_resource_governor_workload_groups AS grps
ON sess.group_id = grps.group_id
WHERE session_id > 60

 

Results

Using Perfmon we can clearly understand what is happening.

Dark blue line is SQLServer:Workload Group Stats: CPU Usage % : SQLCPUSLOW and the pink line is SQLServer:Workload Group Stats: CPU Usage % : SQLCPUFAST. 

At 08:56:50 the SQLCPUSLOW (Dark blue line) workload group gets throttled whereas the SQLCPUFAST group is allowed to use more CPU.





Drop Code


To drop this setup you would run the following code.
USE master
GO
 
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = null)
GO
 
ALTER RESOURCE GOVERNOR DISABLE;
 
USE master
GO
 
DROP WORKLOAD GROUP SQLCPUFAST
GO
 
DROP WORKLOAD GROUP SQLCPUSLOW
GO
 
DROP RESOURCE POOL CPUFAST;
GO
 
DROP RESOURCE POOL CPUSLOW;
GO
 
DROP FUNCTION dbo.checkme