Mainly Task manager, though also resource manager.

This is the set up (using a template from SSMS)

-- ================================================

-- Template generated from Template Explorer using:

-- Configure Resource Governor.sql

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This template creates "admin" workload group

-- which uses "admin" resource pool and creates

-- corresponding classifier function which puts

-- sysadmins in such group

-- ================================================

-- Classifier functions for the Resource Governor must

-- reside within the master database.

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- Create resource pool to be used

CREATE RESOURCE POOL [PoolApps]

WITH (

-- allocate at least <MIN_CPU_PERCENT, int, 10>% of the CPU bandwidth for admin queries

MIN_CPU_PERCENT = 10

-- do not let them exceed <MAX_CPU_PERCENT, int, 30>% either

, MAX_CPU_PERCENT = 20

-- admin queries should be small and we will allocate about <MIN_MEMORY_PERCENT, int, 5>%

-- of the server memory for them

, MIN_MEMORY_PERCENT = 10

, MAX_MEMORY_PERCENT = 30

)

GO

-- TODO

-- Create additional resource pools here

-- Create admin workload group

CREATE WORKLOAD GROUP [AdHoc]

WITH (

-- use all defaults

IMPORTANCE = MEDIUM

, REQUEST_MAX_MEMORY_GRANT_PERCENT = 25

, REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0

, REQUEST_MAX_CPU_TIME_SEC = 1

, MAX_DOP = 2

, GROUP_MAX_REQUESTS = 0

)

USING

[PoolApps]

GO

-- TODO

-- Create additional workload groups here

-- Set the classifier function for Resource Governor

ALTER RESOURCE GOVERNOR

WITH (

CLASSIFIER_FUNCTION = null

)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

ALTER FUNCTION dbo.fn_classifier()

RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

-- Define the return sysname variable for the function

DECLARE @grp_name AS sysname

,@app_name as sysname;

SET @grp_name = 'default';

Set @app_name=Lower(APP_NAME());

-- Specify the T-SQL statements for mapping session information

-- with Workload Groups defined for the Resource Governor.

Select @grp_name=

CASE

WHEN @app_name like '%management studio%'

or @app_name in ('sql query analyzer','ms sqlem')

Then 'AdHoc'

Else 'default'

END

-- TODO

-- Put additional classification logic here

RETURN @grp_name;

END

GO

-- Set the classifier function for Resource Governor

ALTER RESOURCE GOVERNOR

WITH (

CLASSIFIER_FUNCTION = dbo.fn_classifier

)

GO

-- Make changes effective

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

ALTER RESOURCE GOVERNOR RESET STATISTICS

GO

When 'playing around' with the maxdop I usually do this through the GUI, and issue a RECONFIGURE and RESET STATS.