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.
-- Create Pools
RESOURCE POOL CPUSLOW
( max_cpu_percent = 10)
RESOURCE POOL CPUFAST
( max_cpu_percent = 90)
-- Create groups
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
-- Now enable
(CLASSIFIER_FUNCTION = dbo.checkme);
RESOURCE GOVERNOR RECONFIGURE
Run the following code to check if classification is correct.
-- checks connections
sess.session_id, sess.login_name, sess.group_id, grps.
sess.group_id = grps.group_id
session_id > 60
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.
RESOURCE GOVERNOR DISABLE;
RESOURCE POOL CPUFAST;
RESOURCE POOL CPUSLOW;