February 25, 2014 at 12:09 pm
Hi I am looking for help about Resource Governor.
I did a Resource Governor test. I created two pools with 20% maximun CPU (pool LowPriorityPool) and 80% (pool HighPriorityPool). The max cpu% of pool 'default' was 100%, i also created groups and function, and created two accounts. Then I login with different account and executed a SQL at the same time to make CPU busy. In Performance Monitor, I saw the CPU usage% on both pools were 25%. I understand that cpu usage on pool LowPriorityPool might be more than 20% if the system is quiet. But when both accounts execute the SQL at the same time, as my understanding, the cpu usage on LowPriorityPool should lower than 20%, and the cpu usage on HighPriorityPool should be higher, at least higher than 25% and not more than 80%. But why both are 25%?
I am using SQL 2008 R2, Developer Edition, SP1. Windows 7 Enterprise, 64bit, Quad CPU
Please see my test below for details. Appreciate any help.
Use master
go
-- ** step 1 creaet resource pool --
CREATE RESOURCE POOL LowPriorityPool
WITH
( MIN_CPU_PERCENT=20,
MAX_CPU_PERCENT=20,
MIN_MEMORY_PERCENT=20,
MAX_MEMORY_PERCENT=20
);
CREATE RESOURCE POOL HighPriorityPool
WITH
( MIN_CPU_PERCENT=80,
MAX_CPU_PERCENT=80,
MIN_MEMORY_PERCENT=80,
MAX_MEMORY_PERCENT=80
);
-- ** step 2 create workload group by using pool created in setp 1 --
CREATE WORKLOAD GROUP LowPriorityGroup USING LowPriorityPool;
CREATE WORKLOAD GROUP HighPriorityGroup USING HighPriorityPool;
go
-- ** step 3 create classifier function by using groups created in step 2--
CREATE FUNCTION dbo.Classifier_Max_CPU()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
declare @group_name as sysname
set @group_name = (SELECT CASE SUSER_SNAME()
WHEN 'domain\user1' THEN 'LowPriorityGroup'
WHEN 'domain\user2' THEN 'HighPriorityGroup'
ELSE 'default'
END)
return @group_name
END;
GO
-- ** step 4 enable resource govenor --
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.Classifier_Max_CPU);
ALTER RESOURCE GOVERNOR RECONFIGURE;
-- ** step 5 testing --
-- connect to database with different account domain\user1 and domain\user2, execute the
-- sql below and watch CPU usage in Proformance Monitor
-- MSSQL$SQL2008:Resource Pool Stats -> CPU usage % on objects 'HighPriorityPool' and 'LowPriorityGroup' and 'default'
--
SET NOCOUNT ON
DECLARE @i INT
DECLARE @s-2 VARCHAR(100)
SET @i = 100000000
WHILE @i > 0
BEGIN
SELECT @s-2 = @@version;
SET @i = @i - 1;
END
February 26, 2014 at 3:25 pm
any suggestion? thx
February 27, 2014 at 10:14 am
LowPriorityPool should not be 25% CPU as the Max was 20%. May be the classifier function is not working properly and
the requests are getting redirected to the default workload group. Or monitoring could be not correct ..
--
SQLBuddy
February 27, 2014 at 12:02 pm
sqlbuddy123 (2/27/2014)
LowPriorityPool should not be 25% CPU as the Max was 20%. May be the classifier function is not working properly andthe requests are getting redirected to the default workload group. Or monitoring could be not correct ..
--
SQLBuddy
I have checked dm table, the function was configured, please see below. And, when I executed the SQL by using different account, cpu was busing on different pool, so the function was working.
select object_name(classifier_function_id) name, * from sys.dm_resource_governor_configuration;
name classifier_function_id is_reconfiguration_pending
-------------------- ---------------------- --------------------------
Classifier_Max_CPU 1595152728 0
February 27, 2014 at 12:48 pm
If the Classifier function is working as expected, may be there's some issue with perfmon monitoring ..
Try using this report ..
--
SQLBuddy
February 27, 2014 at 1:03 pm
Thanks for your help, I am trying the tool in the link ... let you know later.
February 27, 2014 at 1:25 pm
sqlbuddy123 (2/27/2014)
If the Classifier function is working as expected, may be there's some issue with perfmon monitoring ..Try using this report ..
--
SQLBuddy
Unlucky I got the error message same as the comments below the blog.
February 27, 2014 at 1:38 pm
it seems can run on Windows Server 2008 R2 only.
February 27, 2014 at 1:45 pm
SillyDragon (2/27/2014)
Thanks for your help, I am trying the tool in the link ... let you know later.
They work for me fine. Did you copy them to C:\Users\YourUserName\Documents\SQL Server Management Studio\Custom Reports.
Also did you copy all 3 files .. not just one or two ..
--
SQLBuddy
February 27, 2014 at 1:55 pm
SillyDragon (2/27/2014)
it seems can run on Windows Server 2008 R2 only.
I think it's not like it works only on Win 2008 R2. May be they didn't test it on other OS.
It works for me on Win 7 and Win Server 2012 ..
--
SQLBuddy
February 27, 2014 at 1:57 pm
Yes I did. Can you tell me your OS version, SQL version and CPU information? Thanks
February 27, 2014 at 2:03 pm
sqlbuddy123 (2/27/2014)
I think it's not like it works only on Win 2008 R2. May be they didn't test it on other OS.It works for me on Win 7 and Win Server 2012 ..
--
SQLBuddy
Thanks for reply. i will try sql 2012.
Do you find the result of the custom report is different than the Performance Monitor?
February 27, 2014 at 2:17 pm
I feel it's a better way to do the monitoring and easy to interpret as it's an exclusive report for Resource Governor and less likely to misintrepret ..
--
SQLBuddy
February 27, 2014 at 3:51 pm
Another important thing to note is that the max_cpu_percent very, very rarely actually limits anything to that percentage. In SQL Server 2012 they introduced the additional cap_cpu_percent, which is a hard cap that actually does what you expect.
I understand the desire to have limit that only applies when the server is under load, like max_cpu_percent, but I haven't found it to be all that reliable. The cap_cpu_percent in 2012, on the other hand, works like a charm.
Cheers!
February 28, 2014 at 10:21 am
Jacob Wilkins (2/27/2014)
Another important thing to note is that the max_cpu_percent very, very rarely actually limits anything to that percentage. In SQL Server 2012 they introduced the additional cap_cpu_percent, which is a hard cap that actually does what you expect.I understand the desire to have limit that only applies when the server is under load, like max_cpu_percent, but I haven't found it to be all that reliable. The cap_cpu_percent in 2012, on the other hand, works like a charm.
Cheers!
Great suggestion! I will try. Thanks again
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply