I have written in the past about setting up the resource governor, which I am counting as my favorite new(ish) feature, so I am taking this TSQL2sday post opportunity do my promised followup for monitoring of the resource governor.
Introduction
To start with, my advice about the resource governor is to start small. Have some idea of what you want when you are done… do you need three pools, five pools… how may groups do you envision etc. Once you “think” you know where you want to go, start small. Start with a basic A/B split (user interactive Vs Batch process for example) and work your way up from there, monitoring the whole way. Well balanced, the resource governor works very well… all your resources being consumed in a single pool gets you nowhere (and depending on your max settings can set you back quite a bit)…. so to achieve the “well balanced” lets look at monitoring.
The primary method for monitoring the internals of the resource governor is through the Performance Monitor statistics made available.
Through these metrics you will be able to see both the pool and group level information in much the same way that you would normally see sql server performance statistics, however they are now broken down at a lower level (so you can see how your allocations are performing). Keep in mind an important point about resource governor, if you allocate 10% of the CPU as a max to a pool, that pool can still get 100% of the CPU if nothing else is running on the machine. The resource governor ONLY “kicks in”, holds back resources, splits resources among pools (however you wish to think of it) if there is competition between pools. This makes it important to determine how many, and which resources you are going to assign to the pools. Pool Stats:
I have setup
- Default – PLEASE don’t get rid of the default pool… I had discussion of this in my prior resource governor post.
- Internal – Internal DB processes run here
- adhoc_pool_high – This is where I have my “normal” interactive user queries, reports.
- adhoc_pool_mde – This is where I have the “High Utilization” tools such as SPSS. There are tools that will select large amounts of data for mining or other analysis where runtime is not measured in seconds, and can be separated to prevent those very large queries from dominating the machine while still running in reasonable times.
- load_pool_high – This is where I have batch processes. There is a scheduled job that changes priorities for this pool to a higher-level overnight for my batch-load processes.
- maintenance_pool_low – This is where I run long-running maintenance processes that I just need to get done, but don’t care how long it takes… compressing historical data etc. Things I want to keep out of the way of the rest of the system.
As you can see in the screenshot there is little else running right at this moment, other than the adhoc_pool_high, so in reality that pool has access to 100% of the cpu until there is something that shows up in one of the other pools that needs resources.
Assigned to each of the pools contains one or more workgroups, in this case you can see that the 74% of the adhoc_pool_high is all being used by the “special_user” workgroup. This is where starting simple will help you greatly, there is no association (other than your knowledge… or looking in the database) between workgroups and pools that you can see in performance monitor.
My associations are
Default pool
Default workgroup
Internal pool
internal workgroup
Adhoc_pool_high
Special_user (specific ID’s that I wish to GUARANTEE performance to)
dashboard_user (Interactive BI dashboard that NEEDS to perform well)
User Interactive (interactive reports, management studio, etc_
Adhoc_pool_med
report_user (scheduled reports)
user_tools (General bucket for tools other than interactive queries)
user_spss (SPSS gets it own resource allocation to prevent it from overrunning the machine)
Load_Pool_High
load_batch
Maintenance_Pool_low
maintenance
It is at this level where you will be monitoring the resource allocations to see that they are meeting your expectations. Resource governor is really where art and science meet, you will need to determine which workgroups/pools you wish to give priority to, and which ones will take the back-seat. Once things are running that cross between workgroups that are assigned to different pools you will see the cpu control % numbers take effect.
In addition to the performance monitor stat, which I have personally found most useful for immediate feedback on the performance of the pools there are a few DMV’s that collect information along the way. It is through these DMV’s that you can understand the associations between the workload group and the associated pool, as well as what your configurations are.
dm_resource_governor_workload_groups Where you can see what your allocations/configurations are.
dm_resource_governor_resource_pools Where you can see what your allocations/configurations are.
dm_resource_governor_resource_configuration Is it enabled?
StefBauer's Blog - @StefBauer 
Very good. I have been searching everywhere for anything related to monitoring. I have created several pools and intentionally throttled the default back to 20%. My intention is to put all intended users into the pools I defined and then run a job that would throw an alert if an unplanned user wound up in the default pool. So, I wonder if there is a way to get a list of users that are in a group or a pool.
I will try to get around to writing something for that, it is actually a quite good question! For the quick version, I would start by looking at the DMV’s there are three for the resource gov. (only 2 matter) sys.resource_governor_resource_pools and sys.resource_governor_workload_groups. In addition most every session-level DMV now has a group-id presented in the view such as in sys.dm_exec_sessions and sys.dm_exec_requests. The obvious downside to these is they are reporting on what is currently happening, so if something got to the wrong pool… it has already happened, and if you are not trapping things to a user table regularly you will not see what happened in the past. Unfortunatly there is no simple answer, or at least nothing already built in….
hi!,I likee your writing so much! proportion wwe kkeep up a correspondence extra approximately your article on AOL?
I require an expert on this area to solve my problem.
May be that is you! Having a look ahed to peer you.