October 23, 2009 at 8:09 am
We have a server running two instances of SQL. Not surprisingly, we let a small group of users have adhoc query access to one of the instances, and within a week, one attempted a runaway query that took 100% of the CPU and affected both instances on the server.
I've been looking into Resource Governor, and it seems like a pretty elegant solution, however, I'm unsure how it interacts with multiple instances on a server.
I know I can make an adhoc pool, and restrict those to say, 5% total CPU. It will let them take as much CPU as they need if there's no contention for resources, but if there someone in the default pool needs resources, it will bump them down all the way to only 5% as needed.
I've tested this and it works great.
What I'm not sure about is how this behaves across multiple instances. I know the second instance isn't aware of the first's governor settings, but how does the governor on the first instance respond to demand outside of itself? Is it aware of contention for resources outside of itself, and can it be made to give them up for that as well?
If not, how would others go about solving this issue? I know I can do something like set CPU affinities, and that would probably work fine, but I like the idea of instance A being able to use more resources when needed if instance B is bored.
October 23, 2009 at 8:35 am
I'm not sure, but it seems like you're in an idea position to fire up some simple tests and find out for yourself.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2009 at 10:17 am
AFAIK, the resource governer doesn't understand anything about the other instances.
It's a limiting factor, not a governing factor. If you have two groups, each allowed 70% max CPU, separate instances, they will each fight for the most CPU they can get.
If you have a group set to 5% on one instance, the other instance doesn't have a way to bump that user down to 5%. All it can do is have the instances fight for resources. The only way that the resources are limited for a pool is when other pools on that instance need them. At least that's how I read it.
October 23, 2009 at 12:07 pm
Okay, that seems inline with my testing. It's hard to be sure sometimes, due to the almost black magic nature of resource scheduling, especially when multiple procs and such are brought into the equation.
I tested it on a local box. Instances A and B were set to use just one proc to eliminate variables. Set up an adhoc pool and an adhoc query on instance A, and ran it. It took all the CPU. Ran another query on instance B, and while instance A's CPU use did drop to about 50%, it didn't scale down to around 5% as it was told to do.
I wasn't sure if they were "fighting for resources" or if for whatever reason, the same query on instance B didn't want more than what it was getting.
Looks like my options now are limited to setting CPU affinities, or maybe a timeout or whatever that setting is called where you can make an instance refuse to run any query whose execution plan is over a certain amount.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply