首页 / 数据库 / SQLServer / sql server 2008 R2资源管理器设置

sql server 2008 R2资源管理器设置2014-03-08 csdn博客 ocpyangUSE 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