Introduction

I have a SQL Server that suffers from overnight CPU contention. This system is used in the early hours of the morning so I decided to see what I could do and try to put some order in place by using Resource Governor. I am using SQL Server 2012 Enterprise Edition, so I have this feature available to me, therefore allowing me to specify limits on the amount of CPU that an incoming request can use.

I should state that I cannot change code as this piece of software is a third party product, hence I cannot violate the support agreement. This solution represents a short term control mechanism until the third party make long term changes.

SQL Server Environment

Here is my current SQL Server environment:

• Windows Server 2008R2 Enterprise – SP1 (64 bit)
• SQL Server 2012 Enterprise Edition – SP2 (64 bit) – Default Instance used.
• Processor – Intel(R) Xeon CPU E5-2665 2.40 GHz – 4 cores
• Memory – 24GB RAM DDR3 – 1333MHZ – This is the amount assigned to MAX Memory on the default instance.

The Problem

Overnight there is a mixture of multiple SQL Server Analysis Services (SSAS) cube processing tasks, DBA maintenance tasks, batch processes and general activity that takes place.  For the purpose of this article I reproduced the situation on an exact pre-production equivalent. As you can see from the below Perfmon graph CPU usage is generally high.

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

The next section shows the classifier function, I have put in fake names for obfuscation purposes. What I want to do here is align the SSAS bound work that runs under the DOMAIN\SSASSERVICE to the CPUSQLSLOW pool and my other important batch based tasks and maintenance tasks that runs under DOMAIN\ SQLAGENTSERVICE to my fast pool.

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

Using the following select statements you can then confirm a successful setup.

-- check Metadata
SELECT * FROM sys.resource_governor_workload_groups
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_configuration

The Results

The SSAS tasks now start along with the SQL Agent jobs, batch processes and general activity. This ultimately causes contention; I hope to see resource governor throttle the CPU allocated to SSAS jobs.

First we should check that the classifier is working – looking at the output of the below code snippet I can see that logins are going to the correct pool.

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;

We will now analyze counters from Perfmon to portray a picture.

The main counters of interest are - SQLServer:Workload Group Stats: CPU Usage % : SQLCPUSLOW and SQLServer:Workload Group Stats: CPU Usage % : SQLCPUFAST.

When contention occurs (approx at 13:37:35) you can see via the yellow line (SQLCPUSLOW pool) that resource governor throttles down the CPU usage for the SSAS tasks, whereas the blue line (middle line) being my SQLCPUFAST POOL has been allowed to use more CPU resource. The red line (top line) shows overall CPU usage.

From analysing the above image I can conclude that I have met my goal. This has allowed me to provide some sort of predictable performance on a server that at times suffers from high CPU usage.

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
DROP FUNCTION dbo.DeduceIt

Acknowledgments

I would like to thank Raul Gonzalez for reviewing this article.