sqlserver 2008 R2资源管理器设置

概述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_PE

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,
   [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,
   [Execution Context ID] = ISNulL(t.exec_context_ID,
   [Request ID] = ISNulL(r.request_ID,
   [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

总结

以上是内存溢出为你收集整理的sqlserver 2008 R2资源管理器设置全部内容，希望文章能够帮你解决sqlserver 2008 R2资源管理器设置所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。