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:
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!