You’ve got a bunch of users on the same SQL Server, and you wanna know who’s using the most resources. SQL Server doesn’t make this easy. You can query some management views to see which queries or databases have been using the most resources, but you can’t intuitively tell which USERS are.
To solve this problem, we’re going to use Resource Governor.
Wait. Come back.
When Resource Governor first came out, it didn’t get a lot of adoption. It’s an Enterprise Edition only feature, and its main job is to make queries slower. That’s rather unfortunate, since I don’t have a lot of folks coming to me saying, “Hey Brent, can you make my queries slower?” They don’t need to hire me for that – they have their own business intelligence department to take care of that.
We’re going to use just part of Resource Governor’s functionality:
- Create workload pools as if we were going to cap/limit people’s CPU power
- Create a classifier function so that when they log in, we can put them into different pools
Set limits on each workload pool’s CPU- Use Resource Governor’s reporting DMVs to query who’s been burning up our processors
You can watch me write this blog post, or keep reading for the script and demo version:
Configure Resource Governor to put people into groups.
What you’re about to read is by no means any kind of good practice. Here, I’m just showing you how to quickly configure Resource Governor as a proof of concept. Any database person worth their weight in Query Bucks would read the Resource Governor documentation before actually implementing this in real life. You, dear reader, are not actually worth your weight in Query Bucks, so you’re probably going to just copy/paste this into production. That’s why I like you.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* Create pools for the groups of users you want to track: */ CREATE RESOURCE POOL pool_WebSite; CREATE RESOURCE POOL pool_Accounting; CREATE RESOURCE POOL pool_ReportingUsers; GO CREATE WORKLOAD GROUP wg_WebSite; CREATE WORKLOAD GROUP wg_Accounting; CREATE WORKLOAD GROUP wg_ReportingUsers; GO CREATE WORKLOAD GROUP wg_WebSite USING [pool_WebSite]; CREATE WORKLOAD GROUP wg_Accounting USING [pool_Accounting]; CREATE WORKLOAD GROUP wg_ReportingUsers USING [pool_ReportingUsers]; GO /* For the purposes of my demo, I'm going to create a few SQL logins that I'm going to classify into different groups. You won't need to do this, since your server already has logins. */ CREATE LOGIN [WebSiteApp] WITH PASSWORD=N'Passw0rd!', DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [WebSiteApp] GO CREATE LOGIN [AccountingApp] WITH PASSWORD=N'Passw0rd!', DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [AccountingApp] GO CREATE LOGIN [IPFreely] WITH PASSWORD=N'Passw0rd!', DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [IPFreely] GO /* On login, this function will run and put people into different groups based on who they are. */ CREATE FUNCTION [dbo].[ResourceGovernorClassifier]() RETURNS sysname WITH SCHEMABINDING AS BEGIN -- Define the return sysname variable for the function DECLARE @grp_name AS sysname; SELECT @grp_name = CASE SUSER_NAME() WHEN 'WebSiteApp' THEN 'wg_WebSite' WHEN 'AccountingApp' THEN 'wg_Accounting' WHEN 'IPFreely' THEN 'wg_ReportingUsers' ELSE 'default' END; RETURN @grp_name; END GO /* Tell Resource Governor which function to use: */ ALTER RESOURCE GOVERNOR WITH ( CLASSIFIER_FUNCTION = dbo.[ResourceGovernorClassifier]) GO /* Make changes effective ALTER RESOURCE GOVERNOR RECONFIGURE GO */ |
Eagle-eyed readers like yourself will notice that I commented out the RECONFIGURE at the end. Resource Governor can be pretty dangerous: you can get yourself into a situation where people have trouble running queries because of goofs in the classifier function. Only run that final RECONFIGURE in a dev environment first and make sure you’re getting the results you’re aiming for.
Measure which groups of users are using the most CPU.
To do that, check out the DMV sys.dm_resource_governor_resource_pools:
|
1 2 |
SELECT * FROM sys.dm_resource_governor_resource_pools; |
The result shows you how much CPU each group has consumed:
When you start examining this data, you might find that your first pass of creating pools & groups ends up with not-very-granular reporting data. Maybe 80% of the server’s CPU is burned up by a single group – that’s not very actionable. From there, you want to continue creating smaller and smaller groups until you can figure out exactly who or what application is burning up the most CPU power.
This DMV’s contents reset when the SQL Server service restarts. You’re going to be tempted to hoard this data by logging it every day into a table, and I think that’s a bad idea. Just like you made a lot of bad decisions in your youth when you were young and you needed the money, so did the rest of the people on your team. Every time the SQL Server instance starts up, think of it as a brand new day. The folks writing terrible queries may have learned the errors of their ways and turned over a fresh new leaf, read themselves a good Itzik Ben Gan book on T-SQL, and only written magically well-tuned queries since then.
Ah, probably not.
6 Comments. Leave new
Once you’ve configured resource pools/workload groups you’ll be able to access a lot of great data via performance counters.
If you have infrastructure to collect perf counters this is a very low-friction way of understanding how different applications impact SQL Server – and not just from a CPU perspective (we’ve found counters like active requests, memory grants, and requests completed very useful).
The documentation around some counters is a little lacking, and some of them we were never able to make sense of (the IO counters in particular were sketchy, and a MSFT support ticket didn’t shed much light there).
Ignoring the governance side, we’ve had great success with monitoring via resource pools.
This seems, only moderately useful to me. Like I might be able to say, hey payroll team, you’re using too much CPU. But when they ask which query(s) are (because they have a bazillion different queries), you aren’t going to get that from this.
Wyatt – you’re right, I don’t teach the entirety of performance tuning in this blog post. I really wish I could, but my skills just aren’t quite there yet. Yours might be, though – and I look forward to reading that blog post you write! Sounds awesome.
I don’t think that is the point of it. I think the point is, “hey payroll, you are using too much CPU and it is interfering with every other department in the organization. you have been restricted to 10% CPU time. fix your queries.”
Or just dealing with a really horrendous third party app you can’t do anything about and need to keep it from interfering from everything else.
Even then, in a really busy server, it could be helpful to just know where to start poking. I have an app now that runs so much filth (the entire app is an executable that dynamically constructs sql queries it sends into sql) in my server that the top X worst queries are pretty much always that app and you can’t just pull the low hanging fruit out of blitz cache or whatever
[…] Brent Ozar uses most of Resource Governor: […]
Ooooh that’s genius. Sometimes you want to know how much a login / app is using up overall, and DMVs don’t capture and retain everything, Query Store too (plus no secondaries), traces will usually only be over a certain threshold to keep them lightweight, so getting complete aggregate data on a busy system can be tough. I love it.