When I travel to visit with DBA teams from different customers there are a number of common recurring problems and issues that it seems all SQL Server DBAs are facing as part of their daily routines. It is only natural for a DBA for any data platform to focus on database performance, uptime and business continuity as the 3 main areas of a DBA’s job.
But within database performance and uptime, there is a feature in SQL Server that often do not see utilized enough. That is resource governor. You can search the MSSQLDUDE blog here at WordPress to find my postings about resource governor. One of the best resources in gaining an understanding of resource governor is here at Microsoft.com’s How-To video collection.
There are 2 very important things that resource governor provides a SQL Server DBA that directly help you to produce results day-in and day-out:
1. You can “classify” all incoming connections to your SQL Server by aspects of the connection string from the client like application name, user name, group name, etc. I recommend doing this on your busy production servers even if you don’t intend on necessarily managing resources and throttling those connections. This is because you end-up with a system that can now be managed & monitored more granularly. Instead of monitoring or managing runaway queries or busy connections from a server level, you get a picture of your system based on application, user, group, etc. by Resource Pools which you can see in my perfmon screenshot below:
2. Now that you’ve classified the connections, you can set thresholds on resources and governor your server at a more effective granular level for CPU, memory, MAXDOP and more.
So, what’s new with SQL Server 2012 Resource Governor? There are 3 areas that I think will be very beneficial to DBA when using Resource Governor in 2012 over SQL Server 2008 R2:
- Current CPU throttling only kicks-in when there is contention with other busy connections. SQL Server 2012 resource governor allows you to set a hard cap on CPU for each pool
- SQL Server 2012 has some changes to the memory manager that now allow resource governor to control memory outside of the query grants, which is all that setting MAX memory in resource governor can control today in SQL Server 2008 R2
- You can even now affinitize a resource pool (based on your classifier function) to a scheduler or NUMA node