程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> sql server 2008 R2資源管理器設置

sql server 2008 R2資源管理器設置

編輯:關於SqlServer

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

查看本欄目

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved