Sql-server – Resource Governor on 2014 – CPU Not Being Throttled

resource-governorsql serversql server 2014

We have a specific query ran from our platform that consumes a lot of CPU on a box that already has Enterprise, so Resource Governor seemed like a good solution. I'm working on configuring it on our Dev server, but I'm not seeing the behavior I expected. The server has 4 cores and 16GB of memory with SQL Server 2014 on Server 2012 R2. I modified my resource pool and workload group like so:

USE [master]
GO
ALTER RESOURCE POOL [CRTestRP] WITH(min_cpu_percent=5, max_cpu_percent=5,        cap_cpu_percent=5, 
min_memory_percent=0, max_memory_percent=100, AFFINITY SCHEDULER = AUTO, min_iops_per_volume=0, 
max_iops_per_volume=0)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE 
GO
ALTER WORKLOAD GROUP [CRTestWG] WITH(group_max_requests=0, importance=Low,     request_max_cpu_time_sec=0, 
request_max_memory_grant_percent=25, request_memory_grant_timeout_sec=0, max_dop=1) USING [CRTestRP]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE 

I realize that Resource Governor won't kick in unless there's resource contention. To test the effects, I created a simulated workload on the server (it's a node that holds secondary replicas for Availability Groups, so mine are the only queries against) that raised CPU to an average of 88% using HammerDB. I've verified my test queries are getting correctly put under my workload group by the classifier function, and I have a few test queries ready to go.

After kicking off HammerDB and letting it run for a few minutes, I kicked off one of the CPU intensive queries. It does throttle that one effectively, holding it to ~5% CPU, however, when adding additional connections running the same query, CPU Usage for that workload group shoot up and negatively affect the default pool:

Imgur

In the graph above, CPU is at 100% for the entire time frame. The query in question doesn't seem to be relevent, other than the fact that it would go parallel if not for the MAXDOP 1 on the workload group.

Has anyone else seen this behavior? Has anyone had success with Resource Governor at all? What I really wanted out of it was that these queries wouldn't EVER be able to use above a certain amount of CPU for all of them combined at any one time, but it doesn't look like that's the case. It seems like there should be a configurable value for what CPU % constitutes "contention".

In any case, thank you all for your help!

Best Answer

I actually ended up engaging Microsoft on this. The explanation that the engineer gave is that RG doesn't do a good job at throttling an OLTP vs. OLAP type workloads on the same instance. Our use case was a server with 1000's of tiny queries taking 1-2 ms, with occasional queries taking 6-7 seconds, with the longer queries being the ones we wanted to throttle. He acknowledged this was a bug and they would fix in a future release. Apparently, RG is getting rewritten from scratch in SQL 2016 to resolve these particular issues.