SQL Server 2008 R2 Explorer settings

Use master;

CREATE RESOURCE POOL pmax_cpu_percent_25

With

(max_cpu_percent = 25);

Go

CREATE Workload GROUP gmax_cpu_percent_25

USING pmax_cpu_percent_25;

Go

CREATE RESOURCE POOL pmax_cpu_percent_35

With

(max_cpu_percent = 35);

Go

CREATE Workload GROUP gmax_cpu_percent_35

USING pmax_cpu_percent_35;

Go

CREATE FUNCTION dbo.rgclassifier_max_cpu () RETURNS sysname

With schemabinding

As

BEGIN

DECLARE @grp_name as sysname

IF (suser_name () = ' u01 ')

SET @grp_name = ' gmax_cpu_percent_25 '

ELSE IF (suser_name () = ' u02 ')

SET @grp_name = ' gmax_cpu_percent_35 '

ELSE

SET @grp_name = ' Default '

Return @grp_name

End

Go

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION=DBO.RGCLASSIFIER_MAX_CPU);

Go

ALTER RESOURCE GOVERNOR reconfigure;

Go

ALTER RESOURCE GOVERNOR RESET STATISTICS;

Go

---See if the connection uses Resource Manager

SELECT

[Session ID] = s.session_id,

[User Process] = CONVERT (CHAR (1), s.is_user_process),

[Login] = S.login_name,

[Database] = ISNULL (db_name (p.dbid), ""),

[Task State] = ISNULL (T.task_state, '),

[Command] = ISNULL (R.command, "),

[Application] = ISNULL (S.program_name, "),

[Wait Time (ms)] = ISNULL (W.wait_duration_ms, 0),

[Wait Type] = ISNULL (W.wait_type, '),

[Wait Resource] = ISNULL (W.resource_description, "),

[Blocked by] = ISNULL (CONVERT (varchar, w.blocking_session_id), ""),

[head blocker] =

Case

When r2.session_id isn't null and (r.blocking_session_id = 0 OR r.session_id is null) THEN ' 1 '

ELSE ' "

End,

[Total CPU (ms)] = S.cpu_time,

[Total Physical I/O (MB)] = (s.reads + s.writes) * 8/1024,

[Memory Use (KB)] = S.memory_usage * 8192/1024,

[Open Transactions] = ISNULL (r.open_transaction_count,0),

[Login Time] = S.login_time,

[Last Request Start time] = S.last_request_start_time,

[Host Name] = ISNULL (s.host_name, N '),

[Net Address] = ISNULL (c.client_net_address, N '),

[Execution context ID] = ISNULL (t.exec_context_id, 0),

[Request ID] = ISNULL (r.request_id, 0),

[Workload Group] = ISNULL (G.name, N ") into #tmp01

From sys.dm_exec_sessions s left OUTER JOIN sys.dm_exec_connections c on (s.session_id = c.session_id)

Left OUTER JOIN sys.dm_exec_requests r on (s.session_id = r.session_id)

Left OUTER JOIN sys.dm_os_tasks t on (r.session_id = t.session_id and r.request_id = t.request_id)

Left OUTER JOIN

(

SELECT *, Row_number () over (PARTITION by waiting_task_address ORDER by Wait_duration_ms DESC) as Row_num

From Sys.dm_os_waiting_tasks

W on (t.task_address = w.waiting_task_address) and W.row_num = 1

Left OUTER JOIN sys.dm_exec_requests R2 on (s.session_id = r2.blocking_session_id)

Left OUTER JOIN sys.dm_resource_governor_workload_groups g on (g.group_id = s.group_id)

Left OUTER JOIN sys.sysprocesses p on (s.session_id = p.spid)

Order BY s.session_id;

SELECT

[Session ID] [Session ID],

Login [user name],

[Database] [Database],

application [Application],

[Total CPU (ms)] [CPU],

[Host Name] [Host name],

[Net Address] [IP Address],

[Workload Group] [Load Group]

From #tmp01 WHERE Login (' u01 ', ' u02 ')

--and [Database] = ' order '

DROP TABLE #tmp01

Go

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/