Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


About Resource Govrnor


About Resource Govrnor

Author
Message
SillyDragon
SillyDragon
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 188
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 VARCHAR(100)
SET @i = 100000000
WHILE @i > 0
BEGIN
SELECT @s = @@version;
SET @i = @i - 1;
END
SillyDragon
SillyDragon
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 188
any suggestion? thx
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 2243
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
SillyDragon
SillyDragon
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 188
sqlbuddy123 (2/27/2014)
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


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
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 2243
If the Classifier function is working as expected, may be there's some issue with perfmon monitoring ..

Try using this report ..

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/06/20/sql-server-resource-governor-monitoring-reports.aspx

--
SQLBuddy
SillyDragon
SillyDragon
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 188
Thanks for your help, I am trying the tool in the link ... let you know later.
SillyDragon
SillyDragon
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 188
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 ..

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/06/20/sql-server-resource-governor-monitoring-reports.aspx

--
SQLBuddy

Unlucky I got the error message same as the comments below the blog.
SillyDragon
SillyDragon
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 188
it seems can run on Windows Server 2008 R2 only.
Attachments
Capture.PNG (8 views, 8.00 KB)
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 2243
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
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 2243
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search