It’s a feature I for one have been screaming desperate for – the Sql Server 2008 Resource Governor – the means to finally provide a T-SQL based process to throttle how much CPU and Memory certain activities can utilize.

For me I need to it because I hate bringing the server down every time I need to run a fragmentation scan; with a 1.5TB DB on an undersized server, I need most of the resources dedicated to keeping the application happy. However I don’t want anything I do to kill the performance – and likewise I don’t want some schmuck running a badly written adhoc query in Management Studio preventing me from doing my job.

And this is where the Resource Governor comes in to play by offering Resource Pools and Work Groups. Each Pool can be though of almost as an individual Sql Server instance operating on shared data.And to a certain extent this is true, however the pools do share some meta-data which I’ll explain later.

Out of the box there are 2 Pools and 2 Groups pre-defined – One called Internal which is static and immutable and where Sql Server processes its internal processes, the other called Default which contains a undeletable but otherwise configurable group, also called Default.

In this configuration and user processing is performed in the Default Pool and all internal processing is carried on in the Internal pool – even to the detriment of any other pool out there. This allows Sql Server to continue processing without other pools pressurizing it and as a result, no user processes are allowed in the Internal pool.

Pools are the first level of control and are used to define the minimum and maximum CPU and memory workloads running inside them are ever allowed to use. Work Groups take it to the next level and allow not only a finer level of control but also the ability to move them between pools. Groups, for example can be defined to specify how much of a % they take out of the pool, the priority level and also the MAXDOP setting. Finally, users are associated with pools via use of a Classification Function.

However, it is important to understand that the Resource Governor really only limits resources when there is a pinch point in total resource availability. Running identical queries on an otherwise silent server, one in a High priority pool and the other in a low one will have no obvious effect. However once the server is being pushed the resource governor provides a nice guarantee of resource availability to those sessions that require it and a good throttle to those that don’t.

In the examples that follows I use two new pools and one associated work group in each pool. One pool will be used to process high priority tasks and the other low priority. Two users will be created, one will be assigned to the High Work Group, the other the Low Work Group.

(NB: for brevities sake the users I’m creating here do not have any passwords; it goes without saying that this is exceptionally bad form and should not be practiced on any server that you value data, schema or any other objects on!)

Create Resource Pool Low With ( Min_CPU_Percent = 0, Max_CPU_Percent = 30 ) ;
Create Resource Pool High With ( Min_CPU_Percent = 60, Max_CPU_Percent = 100 ) ;

Create Workload Group Low With ( Importance = Low ) Using Low;
Create Workload Group High With ( Importance = High, Request_Max_Memory_Grant_Percent = 100 ) Using High;

Alter Resource Governor Reconfigure;

Use AdventureWorks2008;
Create Login LowUser With Password=N'', Default_Database=AdventureWorks2008, Check_Expiration=Off, Check_Policy=Off;
Create User LowUser For Login LowUser;
Exec sp_AddRoleMember N'db_owner', N'LowUser'
Create Login HighUser With Password=N'', Default_Database=AdventureWorks2008, Check_Expiration=Off, Check_Policy=Off;
Create User HighUser For Login HighUser;
Exec sp_AddRoleMember N'db_owner', N'HighUser'

As can be seen, the Low pool is only given 30% of CPU to play with – however this is not a true maximum but for these examples it’s good enough. I’ll explain these figures and how they interact later. Notice also the statement reconfiguring the governor; no changes are ever made until this Reconfiguration has taken place.

Next we need to be ability associate users with workgroups – something that’s facilitated with a Scalar-based classification function. This function can make its decision based on numerous factors, including (but not limited to) session user name, host name and application name. For now we’ll keep it very simple and base it off the user name.

Use master;
Go

Create Function    dbo.ResourceGovernorWorkgroupClassifier()
   Returns    sysname
   With SchemaBinding
As
   Begin

       Declare         @WorkgroupName                SysName            = 'default'
       ;
      
       If SUser_Name() = 'LowUser'
           Set @WorkgroupName = 'Low';
      
       If SUser_Name() = 'HighUser'
           Set @WorkgroupName = 'High';

       Return @WorkGroupName;
      
   End
Go

Alter Resource Governor With ( Classifier_Function = dbo.ResourceGovernorWorkgroupClassifier);

This really simple classifier function simply assumes user will be allocated to the “default” work group unless either LowUser or HighUser logs in, at which point they’ll be allocated to the relevant Work Groups. This function will now be executed for every subsequent user login – so as a result you’ll always want to keep this function as short and sweet as possible (a reconfiguration is not required for this to take a effect).

So, now we’re ready to demonstrate the Resource Governor in action. Now, remember earlier I mentioned the governor only kicks in when there’s a pressure point? This being the case we need a method of creating an artificial resource hog – something that will thrash the DB nicely. The AdventureWorks2008.Sales.SalesOrderDetail table is a nice enough size for us to run some hairy and not so hairy queries against. Caveat emptor – the queries themselves are totally nonsensical – they are written to provide example load usage only.


Run the following in a session as a ‘normal’ user (sa, your Windows account or anything else):

Select COUNT(*)
From Sales.SalesOrderDetail As Sod1
Inner Join Sales.SalesOrderDetail As Sod2 On Sod1.SalesOrderDetailID != Sod2.SalesOrderDetailID;

Even on a relatively beefy PC this should begin to the thrash the living bejezus out of Sql Server and take the CPU close to 100%. Now connect a session as LowUser and run:

Set Statistics Time On;
Select COUNT(*)
From Sales.SalesOrderDetail As Sod1
Inner Join Sales.SalesOrderDetail As Sod2 On Sod1.SalesOrderDetailID != Sod2.SalesOrderDetailID;
And Sod1.SalesOrderID < 43800;

Finally, connect a third session as HighUser and run the same query. If all goes well, this last query should finish relatively quickly (for me it takes between 10 and 15 seconds), however the second query (running as LowUser) should take considerably longer (for me it was a shade over 2 minutes). If all is well the first control query should still be running (and still consuming the very soul out of your CPUs). If I were you I’d kill this initial query off now if it’s still going. It’ll do nothing for the performance of your PC!





To see the true affect of the Governor, one simply has to compare the results produced by the time statistics. Whilst the actual CPU used by both queries is broadly the same, the elapsed time for low user is almost exponentially higher. This is directly the effect of running under the pool where the maximum CPU is capped at 30%. The figures here also show running the same queries under no stress to show that without putting the server under load, the governor makes very little difference.


Finally for this post (and don’t worry – there will be a follow-up posting detailing some of the more intricate aspects of the Resource Governor) we’ll demonstrate another nice feature – moving Work Groups to different pools.

Moving a work group can be performed a any time, however the effect will only be felt with a Reconfiguration. Executing the following will result in an error:

Alter Workload Group Low Using High;
Alter Resource Governor Reconfigure;

Msg 10904, Level 16, State 2, Line 2

Resource governor configuration failed. There are active sessions in workload groups being dropped or moved to different resource pools. Disconnect all active sessions in the affected workload groups and try again.

What is needed here is to disconnect any sessions currently utilizing the Low Work Group. We can utilize the following query to find out which sessions are affected and kill them off by hand (and don’t forget to run the reconfiguration!)

Select    Session_Id, Status
 From    sys.dm_exec_sessions As S
       Inner Join sys.dm_resource_governor_workload_groups As G
           On S.group_id = G.group_id
 Where     G.name = 'Low';

Now repeat the experiment by starting the Stress query first followed by the Low and finally the High.



As you can see the elapsed time is significantly less when the Work Group is moved into the High Resource Pool. The reason for the discrepancy between the two is down to the configuration of the two groups; the High Group is set at a High Priority and allowed to utilize all of the RAM the pool has been allocated, whereas the Low Work Group is limited to being able to grab a maximum of 25% of memory from the pool (the default) and a relative importance setting of Low.

That’s all for now – keep tuned for part two where we delve deeper into the mind of the guv’nor.

Sql Server 2008 Build 10.0.1600.22 (RTM)