Blitz Result: Resource Governor Enabled
SQL Server’s Resource Governor can change the behavior of queries by throttling users. They’ll only get limited CPU or workspace memory.
Once you put it in place, though, it’s tough to be aware of exactly which users are being slowed down, and by how much. You might be spending valuable time trying to performance tune a query when in reality the Resource Governor is the real problem. We rarely stop to think about Resource Governor – especially if we don’t know that it’s been enabled.
The Resource Governor Can Backfire
Things aren’t as simple as they might seem when it comes to the Gubner:
- If someone wasn’t super careful when writing the classifier function, performance for everyone can be slowed down.
- The Resource Governor doesn’t prevent throttled queries from impacting others. By slowing them down, it might mean that they hold locks for longer and actually cause problems for other queries for longer than they would otherwise.
- Resource Governor doesn’t govern all query memory. It only governs query workspace memory used for sorts, joins, and other operations. It doesn’t stop a query from using large amounts of SQL Server’s data cache. Similarly, it doesn’t stop a query from potentially filling up tempdb.
This part of our SQL Server sp_Blitz® script checks sys.resource_governor_configuration looking for is_enabled = 1.
To Fix the Problem
Review why you have Resource Governor configured, test your classifier function, and make sure your performance fix isn’t slowing you down. Use the step below to turn off the Resource Governor if needed.
How to Turn off the Resource Governor
If you don’t need to throttle users or groups, the easiest way to fix this is simply to run an ALTER RESOURCE GOVERNOR DISABLE query. Your configuration will still be saved, but the Resource Governor will just stop throttling queries.