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

Advertisements

Author: Protiguous

C# Software Developer, Father, and seeker of Truth.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s