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.
-- ** step 1 creaet resource pool --
CREATE RESOURCE POOL LowPriorityPool
CREATE RESOURCE POOL HighPriorityPool
-- ** step 2 create workload group by using pool created in setp 1 --
CREATE WORKLOAD GROUP LowPriorityGroup USING LowPriorityPool;
CREATE WORKLOAD GROUP HighPriorityGroup USING HighPriorityPool;
-- ** step 3 create classifier function by using groups created in step 2--
CREATE FUNCTION dbo.Classifier_Max_CPU()
declare @group_name as sysname
set @group_name = (SELECT CASE SUSER_SNAME()
WHEN 'domain\user1' THEN 'LowPriorityGroup'
WHEN 'domain\user2' THEN 'HighPriorityGroup'
-- ** 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
SELECT @s = @@version;
SET @i = @i - 1;