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

 

---查看连接是否使用资源管理器

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 IS NOT 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] [会话id], Login [用户名], [Database] [数据库], Application [应用程序], [Total CPU (ms)] [cpu], [Host Name] [主机名], [Net Address] [IP地址], [Workload Group] [负荷组]  FROM #tmp01 WHERE Login IN ('u01','u02')  --AND [Database]='order'  DROP TABLE #tmp01  go