Resource Governor in SQL Server

SQL Server 2008 has a new feature called Resource Governor.   You can limit how much CPU bandwidth or memory certain processes will consume.

Configuration comprises the following basic steps:
  1. Create and configure a Resource Governor resource pool that limits either the maximum average CPU bandwidth that will be given to requests in the resource pool when CPU contention occurs or the maximum memory

    CREATE RESOURCE POOL pool_name
    [ WITH
        ( [ MIN_CPU_PERCENT = value ]
        [ [ , ] MAX_CPU_PERCENT = value ]
        [ [ , ] MIN_MEMORY_PERCENT = value ]
        [ [ , ] MAX_MEMORY_PERCENT = value ] )
    ]
    [;]
  2. Create and configure a Resource Governor workload group that uses this pool.

    CREATE WORKLOAD GROUP group_name
    [ WITH
        ( [ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
          [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ]
          [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ]
          [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ]
          [ [ , ] MAX_DOP = value ]
          [ [ , ] GROUP_MAX_REQUESTS = value ] )
     ]
    [ USING { pool_name | "default" } ]
    [ ; ]
  3. Create a classifier function, which is a user-defined function (UDF) whose return values are used by Resource Governor for classifying sessions so that they are routed to the appropriate workload group.

    Here's an example where activity in SQL Server is classified based on a mix of who the user is and also what the application is:

    CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
    WITH SCHEMABINDING
    AS
    BEGIN
        DECLARE @grp_name sysname
          IF (SUSER_NAME() = 'sa')
              SET @grp_name = 'GroupAdmin'
          IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
              OR (APP_NAME() LIKE '%QUERY ANALYZER%')
              SET @grp_name = 'GroupAdhoc'
          IF (APP_NAME() LIKE '%REPORT SERVER%')
              SET @grp_name = 'GroupReports'
        RETURN @grp_name
    END;


  4. Register the classifier function with Resource Governor.   Apply the changes to the Resource Governor in-memory configuration.

    ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier)
    ALTER RESOURCE GOVERNOR RECONFIGURE
    GO

MSDN article:   http://msdn.microsoft.com/en-us/library/bb933866.aspx