SQL 2008 ships with a very cool feature called the resource governor. Of course, the resources we can control in this release are CPU and memory (unfortunately we can not control IO yet). Anyway, if you have ad-hoc reporting or poorly written batch processes that are killing your transaction bound users then you can create some rules to throttle back the heavy hitters. Here are the steps:

Step 1. Create a Resource Pool and a Workload Group

CREATE RESOURCE POOL PoolAdHocReporting
CREATE RESOURCE POOL PoolWarehousing
GO
CREATE WORKLOAD GROUP GroupReporting USING PoolAdHocReporting
CREATE WORKLOAD GROUP GroupWarehousing USING PoolWarehousing
GO

Step 2. Create a Classifier Function

The classifier function maps users to the workload group. Basically you are putting your users in buckets so you can apply rules to them.

CREATE FUNCTION CLASSIFIER_FUNC()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
     DECLARE @val varchar(32)
     if 'ReportUserAcct' = SUSER_SNAME()
        SET @val = 'GroupReporting';
     else if 'WarehousingAcct' = SUSER_SNAME()
        SET @val = 'GroupWarehousing';

     return @val
END
GO

Step 3. Tell the Resource Governor to Use Our Classifier Function

ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.CLASSIFIER_FUNC)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Now at this point we have enabled the resource governor and applied our classifier function however we have not throttled any resources yet. So now its time to modify CPU and memory limits.

Step 4. Modify Resource Limits

Now you can use scripts to modify Memory and CPU but for the purposes of this demo I think it is easier to modify settings with management studio. To view the Resource Governor Properties expand the management folder of your instance and right click on the Resource Governor node and select Properties. You should now see this dialog.

Step 5. Laugh when End Users Say They Don't Have Time To Tune Their Queries

With the resource governor on your side you now have a little more leverage over your end users. If they refuse to tune their queries then just throttle their resources. Let's face it, most of us DBAs are constantly trying to identify and fix rogue queries on our servers. Sometimes queries are easily identified and fixed by adding new indexes, updating some stats or re-writing the T-SQL. Unfortunately, there are times when you have to resort to something like the resource governor in order to get things done.