1

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!

Improve this question
3
  • Yes, of course, Resource Governor works. How exactly have you validated that the user you're connecting as is being properly identified by your classifier function?
    – Aaron Bertrand
    Sep 23, 2014 at 0:34
  • 1
    Thanks for taking a look Aaron. I created a new SQL user and my classifier function keyed off SUSER_NAME() to put that user's sessions in the CRTestWG workload group. I then queried dm_resource_governor_workload_groups to find the group_ID for my workload group, then queried dm_exec_sessions for that group_ID. The only sessions in that group_ID were the two sessions I was running with the new sql user.
    – cplax14
    Sep 23, 2014 at 16:39
  • My problem is similar to yours. I explained at that link : stackoverflow.com/questions/31674302/…
    – Banu Akkus
    Jul 30, 2015 at 9:25

1 Answer 1

Reset to default
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.