|
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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
