SQL Server Resource Governor - SQLServerCentral

My Aim

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.

The Solution

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.

T-SQL Setup

Here is the T-SQL I used to set up the Resource Governor pools.

USE master
GO
-- Create Pools
CREATE RESOURCE POOL SQLCPUSLOW
WITH
(
   max_cpu_percent = 10
)

CREATE RESOURCE POOL SQLCPUFAST
WITH
(
   max_cpu_percent = 90
)
GO

The below code then binds the pools created to workload groups.

-- Create groups
CREATE WORKLOAD GROUP SQLCPUFAST
  USING SQLCPUFAST;
GO
CREATE WORKLOAD GROUP SQLCPUSLOW
  USING SQLCPUSLOW;
GO

Clean Up
If you need to start again you would use the below T-SQL.
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 SQLCPUFAST;
GO
DROP RESOURCE POOL SQLCPUSLOW;
GO