Resource Govenor
-
I am trying to limit the CPU on one of my databases. On a test machine I have created the following.
/****** Object: ResourcePool [a] Script Date: 12/05/2013 13:11:57 ******/
CREATE RESOURCE POOL [a] WITH(min_cpu_percent=0,
max_cpu_percent=10,
min_memory_percent=0,
max_memory_percent=100)
GO
/****** Object: WorkloadGroup [a] Script Date: 12/05/2013 13:12:42 ******/
CREATE WORKLOAD GROUP [a] WITH(group_max_requests=0,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=0) USING [a]
GO
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[fn_classifier] Script Date: 12/05/2013 13:13:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_classifier]()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
RETURN N'a'
END
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fn_classifier]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
I am then executing a large long running query.
By running
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
where CAST(s.host_name as nvarchar(20)) LIKE 'mymachine%'
ORDER BY g.name
GO
I can confirm that my query is using the new work load group - group 'a'.
However my CPU still max out at 100%.
Can you please advise where I may be going wrong. Thanks
-
http://blogs.msdn.com/b/psssql/archive/2008/01/10/sql-server-2008-resource-governor-questions.aspx
According to this link, the resource governor only limits the CPU if there is contention, so if nothing else is using CPU the limits you have set will be ignored.
-
Thank you very much. I had misssed that - but it makes perfect sense.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply