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.

We check 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.

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.