Resource Governor – How to get started?


 

Here we are, as promised, with some rumblings on SQL Server 2008 Resource Governor.


 


I've played a lot with the various options which this features provides, especially in preparation of my TechEd presentation last week! Did I ever mention how much I loved it :-)? Yeah, I guess too much already. Ok, so this is the last time. Maybe. Umh…


 


Moving on, so what if you want to start using Resource Governor, let's say… today? 🙂


 


As usual, I don't like to repeat what others have already been posting, possibly in a better way that I would do really, so here are first some resources to check out to start learning about it:


 


First place, of course, Technet: http://technet.microsoft.com/en-us/library/bb933866.aspx


A bit more advanced, from PSS Engineers: http://blogs.msdn.com/psssql/archive/2008/01/10/sql-server-2008-resource-governor-questions.aspx


 


So what to add on that?


 


I guess the tricky bit would be to understand how to effectively implement Resource Governor on your specific scenarios.


 


Of course there are some typical scenarios in which you'll most likely use it. Amongst the ones I use to mention:


 



  • SQL Server 2008 Backup Compression - as I posted some time ago (http://blogs.technet.com/beatrice/archive/2008/06/11/data-backup-compression-in-sql-2008.aspx), backup compression is a brilliant new feature which comes with SQL Server 2008 (yep - always enterprise - though you can restore on whichever version). It saves you money (for third party products), storage space, and it allows you to reduce the run time required from your backups and restores. Yeah… and…??? And you pay all these benefits in terms of CPU. So. If CPU is a issue on your implementation, or better, if CPU is a resource which you want to govern on your implementation and you're not bothered on having your backup\restores taking a little bit longer, well Resource Governor is your friend.  Set up a pool and specify a max memory, call it however you want (Backup Compression Pool I guess is a good idea :-)), and run your compressed backups with a specific user which you can isolate through your Classifier Function. Deal done.

 



  • Reporting Scenarios. Does this ring a bell? Correct - Resource Governor governs only the Database Engine, not SQL Server Integration Services, Analysis Services, Reporting Services. OK. BUT. Your Reporting Services or Reporting Application (oh no - are you NOT using Reporting Services yet? - we'll have a word about this maybe in another post) well you'll have to retrieve data from the Database Engine at some time. Correct. And what if that reporting application is very critical to your business or especially - what if you want a quiet day without your customers phoning you to complain that the application is slow? Nearly the same as before. This time though your pool will have a minimum CPU and Memory, and again the deal is done by isolating the user name or the application name in the classifier function.

 



  • DBAs. Ohhhhhh yeah. DBAs have LOADs of work to do during the day (of course, I am nearly a DBA so I better save my job :-)). So, what do they have to do? Troubleshoot and monitor, so they really need a minimum amount of CPU and Memory to play around but also we still want our business users to be happy with our implementation, so we do not want DBAs to take up all the resources on our server. So this time we'll be using MAX and MIN values for our CPU and Memory.

 



  • LOL - I love this - and what if you have a "crap application"? I've been asked this question at TechEd. I'm afraid a crap application will still remain a crap application. FIX IT! But yeah, whilst fixing it, if your application is so crap to fill up all you resources, TEMPORARLY you can assign max values for it in the Pool, or if for example you have to deal with locking, you can grant MINIMUM values to have  your applications queries to run as fast as they can, to leave other workload free to run asap. Again, this is not the way Resource Governor is meant to be used and especially, I am talking generally, so TEST before doing anything in production.

 



  • So what if you have anything else? I mean most of your SQL Server implementations will not just be about Backups, Reporting and DBAs (I'm not even considering crap apps :-)). How can you understand how to benefit from Resource Governor in your specific scenario?

 


It's always very difficult to talk generally, but I would personally approach the problem in the following way:


 



  • As I will generally know my implementation and the users and apps which are connecting to it, I will set up pools (remember max 18 user-defined) and workload groups (1:1 relationship) with default parameters, plus classifier function to assign each workload to a specific pool.

  • In this way - with default parameters - I will obtain SQL 2005 behavior BUT I will be able to monitor pools usage (and therefore CPU and Memory Usage per Pool) with the new events available in PerfMon and the new DMVs dedicated to Resource Governor

  •  I can then start changing my default parameters based on monitoring, benchmark and monitor once again till I will achieve the desired results.

 


I will soon post about some other questions I've been asked during TechEd! Stay tuned!


 


- Beatrice Nicolini -

Comments (2)
  1. Anonymous says:

    One of the most common question I’ve received regarding data and backup compression in SQL Server 2008

Comments are closed.

Skip to main content