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.
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
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_
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)
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?