Not to long ago I sat through two sessions on the resource governor. One of the sessions was delivered by a Microsoft employee and another was delivered by a non Microsoft employee. Both speakers did a great job in describing what the Resource Governor was supposed to do. It’s really going to be some great functionality added, the key part to this as most of the aspects of SQL Server is that everyone needs to understand the limitations to the feature.
Let me digress a bit. The resource governor is a feature that will allow you as a DBA to decide who and or what get how much of what resource. In an example that I find most helpful is thinking about a company that can really only afford one server, However there is the occasion where they need to run some heavy duty reports on this server, lets say an end of the year inventory. Without the resource governor there could be a lot of the memory and the processors used on compiling this information. A Join would have to be done on what was sold, and what was shipped, received, returned so on and so on.
The resource governor in SQL Server 2008 gives the impression that I can say that if a user we will call him JOE_Reporter, logs on to the server that at no time will he take up more then let’s say 10% of the memory. This can be almost configured this way based on what I saw of the demos. Here is the catch that I saw. The resource governor will allow JOE_Reporter to use more then 10% of the memory if the memory is available. So reflect back to our example where we have to have an end of year inventory report run. If JOE_Reporter shows up early that day because he knows he has a lot to get done, and its an hour before everyone else starts working on the system the memory may be there for him. Joe_Reporter does what any hard charger would do and fires off the report. The Memory shoots up to 100% used and sits there. When the rest of the staff comes in they are not going to have the memory that the DBA’s told them they would during the speech this is why we are upgrading.
The point that I am trying to make is not that we should not use the resource governor or that you should not upgrade to SQL Server 2008. There are a lot of really good tools there that can be used. The resource governor is one of them. The main focus of this little rant is that we need to make sure we know all aspects of a feature before we put it in place. With that all being said, there is a lot more testing I would like to do on the resource governor before I say it’s the best thing since sliced bread.