Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

About Resource Govrnor Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 12:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:58 PM
Points: 29, Visits: 128
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
Post #1545102
Posted Wednesday, February 26, 2014 3:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:58 PM
Points: 29, Visits: 128
any suggestion? thx
Post #1545614
Posted Thursday, February 27, 2014 10:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:19 AM
Points: 1,194, Visits: 2,215
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

Post #1545990
Posted Thursday, February 27, 2014 12:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:58 PM
Points: 29, Visits: 128
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
Post #1546059
Posted Thursday, February 27, 2014 12:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:19 AM
Points: 1,194, Visits: 2,215
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
Post #1546085
Posted Thursday, February 27, 2014 1:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:58 PM
Points: 29, Visits: 128
Thanks for your help, I am trying the tool in the link ... let you know later.
Post #1546094
Posted Thursday, February 27, 2014 1:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:58 PM
Points: 29, Visits: 128
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.
Post #1546106
Posted Thursday, February 27, 2014 1:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:58 PM
Points: 29, Visits: 128
it seems can run on Windows Server 2008 R2 only.

  Post Attachments 
Capture.PNG (6 views, 8.69 KB)
Post #1546110
Posted Thursday, February 27, 2014 1:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:19 AM
Points: 1,194, Visits: 2,215
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
Post #1546114
Posted Thursday, February 27, 2014 1:55 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:19 AM
Points: 1,194, Visits: 2,215
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
Post #1546119
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse