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.
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.
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.
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 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.
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
I would like to thank Raul Gonzalez for reviewing this article.