I'm looking for a method to, in real-time, automatically, adjust Resource Governor settings.
Here's an example:
Imagine that I have 10 applications, each hitting a different database on the same database machine. For normal operations, they do not hit the database very hard, so I might want each one to have 10% CPU power reserved.
Occasionally, though, one or two of them might spike, and run an operation which could really use the extra power to run faster. I'd like to be able to adjust to compensate (say, reducing the non-spiking apps to 3%, and splitting the difference between the spiking apps).
This is a kind of poor man's method of trying to dynamically adjust resource allocation and priorities.
Scripts (or something script-like) is preferred, since the requirement is for meta-level adjustments to be possible in real-time, also.
Since most of the Resource Governor tasks can be done through T-SQL I'm assuming you could script something like this. If you know a particular resource group spikes a certain time of the day you could create a job that would run at that time to assign it some extra resources. If it's just spiking at random times I'd recommend simply allowing it more resources on a permanent basis as it would be difficult and potentially dangerous to have these changes happen on the fly.