0

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.

blueberryfields
  • 757
  • 1
  • 7
  • 17
  • could you explain a bit more about your desired goal? are you looking to alter the RG dynamically while applications are running? So, if you set a cap of 25% CPU, you want to be able to have it automatically be bumped to 35%? – SQLRockstar Mar 14 '10 at 03:48
  • 1
    If none of the applications hit the database hard then why bother with resource governor? If you don't have run-away queries, sql will automatically give each instance what it needs. Having 10 RG with 10% a piece would (it seems to me) artifically limit the performace of the server as a whole. – Jim B Mar 15 '10 at 15:46
  • @Jim B: It does seem a bit strange but keep in mind that resource governor is only supposed to kick in when it's needed. If none of the databases are being heavily used at the moment then any single one of them should have full access to the resources on the server. – Shane Mar 15 '10 at 17:40

1 Answers1

0

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.

Shane
  • 1,869
  • 4
  • 20
  • 34
  • I can't predict the spikes, and I don't have the resources available to increase allotment to all the applications at the same time :( – blueberryfields Oct 14 '10 at 23:13
  • Then I'd say your best bet is to try and monitor which applications tend to spike the most and simply make sure they are assigned the resources they need for when it happens. If all of your applications spike at random and it's causing large problems over time for you, you may want split them up onto separate hardware. – Shane Oct 15 '10 at 13:55
  • Looks like this is the best that can be done with SQL Server 2008 RG. – blueberryfields Nov 26 '10 at 18:59