March 19, 2014 at 11:21 am
Is there an easy way to limit resources for all users on a SQL server with resource governor vs setting up a workload for each user? IE, I want to limit each login to 25% of the CPU for example.
March 20, 2014 at 11:07 am
Well, it's not pristine, and I wouldn't recommend doing it, but in theory this is possible, yes. You could create 4 workload groups with their own resource pools using a cap_cpu_percent (only available if you're on 2012) of 25%, and make the classifier function assign incoming user spids to workload groups in a sort of round robin fashion by basing assignment on @@spid modulo 4.
That would make it so that each session could use no more than 25% CPU (give or take a few, since the cap isn't perfectly precise). Of course, there a bunch of problems, too. For one, the same login might open several sessions, and then an approach like the above that is based on sessions would still let one login max out CPU.
I think the bigger question here is why you would want to do this, though. Certainly there are going to be queries that will benefit from using more than 25% CPU and will not adversely affect other sessions on the instance. Having said that, I don't know what your workload is like or what requirements you are trying to meet. My guess is that there will be much better ways of doing what you want to do, but I can't be sure.
A cautionary tale about this (longish):
I've done a lot of testing of various "clever" schemes like the above to control CPU utilization, and performance is often affected in strange ways.
Just as an example, one of my favorite ways to quickly stress CPU on a test server is to create a moderately sized table, and run a select count(*) from that table into a variable within a WHILE 1=1 loop. With default cost thresholds and moderately large tables, the count(*) will typically run in parallel and push the CPU to 100%.
Now, without fiddling with any of the resource governor settings, the particular count(*) query on the test server in front of me is averaging 1215 ms of CPU time, and 313 ms of elapsed time. I have 4 cores, so that's about what you'd expect.
If I force it to run with maxdop 1 (effectively only 25% on my 4 core system), it averages 1092 ms of CPU time, and 1089 ms of elapsed time (all these averages are over 10 runs).
Now the fun part. If I set up resource governor as described above, and run the same test count(*) as in the first run (no maxdop hint), it now averages 1357 ms of CPU time, and 1534 ms of elapsed time. The elapsed time in particular has increased about 50% from the maxdop 1 query, which also only used about 25% of CPU.
The elapsed time is also highly unpredictable in the last run, varying from as low as 1140 ms to as high as 2100 ms.
The behavior is even worse if I run two copies of the test query simultaneously. Without resource governor, I max out my CPU, and the queries, which are now contending for CPU resources, average about twice as long in elapsed time as before, about 680 ms instead of the 313 ms.
What happens if I run these contentious queries simultaneously WITH resource governor set up as described? Well, I am no longer maxing out my CPU (only about 50% utilization), but the average elapsed time is now up to 2100 ms, a performance degradation of 7x.
I've managed to make CPU utilization look better on a graph I might show, but I've actually managed to destroy my performance.
The upshot: yes, it can be done, but at least in some cases it will hurt far more than it helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply