This entire piece was a joke for April 1, 2013. I hope you read it with that in mind and enjoy it - Steve Jones

The Resource Governor in SQL Server is a great way to control workloads for groups of users that might have different processing needs. In SQL Server 2012, the Resource Governor allows administrators to throttle both CPU and query memory for groups of users. However there is a little known additional capability in the Resource Governor. This article will show you how to "slow down" or limit the resources for a query that is already running. This can be a boon to administrators that want to allow ad hoc querying, but do not want long running queries to unnecessarily slow down your instance.

Overview

The Resource Governor is a feature in SQL Server that allows the amount of CPU and query memory to be limited for groups of users. The Resource Governor system is always running in SQL Server. However by default all users fall into the default workload group and there are no limits on processing resources for the group. There is also an internal group that is used for system tasks.

An administrator can write a classifier function that runs when a user logs in. This function can assign the login to a workload group, which may (or may not) have resource constraints placed upon in. In SQL Server 2012, you can limit the maximum and minimum levels of CPU as well as introduce a hard cap on the amount of CPU a resource can consume. You can read more about this system in the 2012 white paper or Books Online.

Runaway Ad Hoc Queries

However, even if you assign logins to a workload group, it is possible for one of the logins to submit a query that uses up all of the processing capability for that workload group. This means that other logins in this group could have their queries starved for resources, somewhat defeating the purpose of the Resource Governor.

As an example, suppose you have a report runner group that is allowed to use up to 90% of the CPU on the instance. You reserve 10% for administrative tasks, or developers that may be deploying new reports. One of your report runner logins submits a very large report, requesting complex aggregations for the last ten year's worth of data. This is a valid request, and it needs to complete, but during the day it's preventing other users from having their queries executed on the system.

You could assign this login to a new workload group, limit the CPU to 20%, and cancel the query, asking the user to reconnect and resubmit it. However that's annoying, and it doesn't necessarily help when you have a report that a number of people may run. In that case, are you planning on moving people among workload groups on a regular basis? Have a shared account for this report (never a good idea)?

The Little Known Solution

There's a trace flag in SQL Server that can help. If you enable trace flag 707, you can add dynamic CPU throttling to a particular SPID. In this way, you can "slow down" a query that's using too much of your CPU. Note that this is only for CPU throttling.

Here's how this would work in practice. You receive a call that Allen in the Finance group is running a large report and the server is slow. Using a tool like sp_WhoIsActive, you find Allen's SPID is 48. He's in a workload group already that is limited to 80% of CPU, but that isn't helping here since Allen's query is slowing down all people in Finance.

You run this code:

DBCC TRACEON (707, 48, 40)

Where the parameters are as follows:

• First: the trace flag, 707 in this case. 
• Second: the SPID of the user you need to throttle.
• Third: the CPU maximum this connection is allowed to use.

These result in Allen's query slowing down as it can only use 40% of the total CPU on the system for it's execution. As a result, on a busy server, the other users in the same workload group as Allen would have the other 40% (80-40) to use for their queries and other workload groups would end up with 20%, assuming you have Resource Governor configured to give this workload group it's entire 80% of the CPU.

Once Allen's connection closes, this setting disables itself until you need it again.

There are a few caveats with this feature. You should understand that this feature should be used carefully and sparingly. The CPU caps are confusing to calculate and using this for a number of SPIDs at one time might result in unpredictable behaviors. As this is a part of Resource Governor, it is only available in Enterprise Edition and since it's April 1, this is a complete fabrication. Hopefully you realize this feature, while potentially helpful, does not exist.

The use of Resource Governor to manage your systems and control the resource allocation among groups of users, especially in a multi-tenancy situation, is a valuable addition to your DBA toolkit. If you have disparate resource requirements that compete with each other and impact performance, consider learning about Resource Governor and testing it in your environment.

Conclusion

Experienced Administrators will appreciate the ease with which you can dynamically alter the Resource Governor workloads using this little known trace flag in SQLServer. If you find that Resource Governor is not working at a granular enough level or you have too many workload groups to manage, consider testing this in your Resource Governor setup as a potential solution.

Note that all code and examples provided here are not guaranteed to work in your environment. Please test changes and settings for yourself.