I want to throttle down the CPU assigned to SSAS based processes allowing my other tasks to also use CPU during contention, this is because the SSAS tasks has a negative impact on my other workloads.
I decided to create two pools, a so-called slow one (called SQLCPUSLOW) and a fast one (called SQLCPUFAST). The SQLCPUSLOW pool was assigned a maximum of 10% of CPU during contention periods and the fast pool I had assigned 90%. Please note, as stated by Boris Baryshnikov, “When you specify percentage on a pool it ensures average CPU bandwidth distribution on per scheduler basis for CPU bound workloads”. (http://blogs.technet.com/b/sqlos/archive/2008/01/18/part-2-resource-governor-cpu-demo-on-multiple-cpus.aspx).
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.
Here is the T-SQL I used to set up the Resource Governor pools.
-- Create Pools
CREATE RESOURCE POOL SQLCPUSLOW
max_cpu_percent = 10
CREATE RESOURCE POOL SQLCPUFAST
max_cpu_percent = 90
The below code then binds the pools created to workload groups.
-- Create groups
CREATE WORKLOAD GROUP SQLCPUFAST
CREATE WORKLOAD GROUP SQLCPUSLOW
Clean UpIf you need to start again you would use the below T-SQL.USE master
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = null)
ALTER RESOURCE GOVERNOR DISABLE;
DROP WORKLOAD GROUP SQLCPUFAST
DROP WORKLOAD GROUP SQLCPUSLOW
DROP RESOURCE POOL SQLCPUFAST;
DROP RESOURCE POOL SQLCPUSLOW;