SQL Server Resource Governor Webcast – Demo Scripts

Hi Friends,

Last week I took a webcast “Tap is the only Solution Ver.2 (SQL Server Resource Governor)“. The webcast was very well received and there were a lot of questions regarding the Demo and the scripts. So in this blog post I’ll provide all the scripts to implement, test, tweak & remove Resource Governor. You can also use these scripts to do Proof of Concept (POC) on Resource Governor.

Hope this will help you.

Happy Learning\Testing – DO leave a comment to let us know how we are doing.

Implement\ configure Resource Governor

Create database simple
go
---Login creation for all users, I disabled strong password checking for demo

--purposes only but this is against best practices
	 
USE [master]
GO
CREATE LOGIN [Sales_app] WITH PASSWORD=N'sales_app', DEFAULT_DATABASE=[simple], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
	 
CREATE LOGIN [Reporting_app] WITH PASSWORD=N'reporting_app', DEFAULT_DATABASE=[simple], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
	 
CREATE LOGIN [Fin_App] WITH PASSWORD=N'fin_app', DEFAULT_DATABASE=[simple],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
Go
	 
use simple
Go
CREATE USER [Sales_app] FOR LOGIN [Sales_app]
CREATE USER [Reporting_app] FOR LOGIN [Reporting_app]
CREATE USER [Fin_App] FOR LOGIN [Fin_App]
GO
	 
--limiting resources to test Resource Governor because I am using a laptop with 8 proc and 8GB ram

--which will be too much and complex to do POC
	 
sp_configure 'show ',1
go
reconfigure
go
sp_configure 'min server', 2048;
go
sp_configure 'max server', 2048;
RECONFIGURE
GO
	 
-- create user pools
-- note that we are using all default parameters
CREATE RESOURCE POOL Pool_Sales_fin_app
CREATE RESOURCE POOL Pool_reporting
	 
-- create user groups also note that all groups created with default parameters only pointing to the

--corresponding pools (and not 'default' pool)
CREATE WORKLOAD GROUP Sales_Group
USING Pool_Sales_fin_app
	 
CREATE WORKLOAD GROUP Reporting_Group
USING Pool_reporting
	 
CREATE WORKLOAD GROUP Fin_Group
USING Pool_Sales_fin_app
GO
 
-- now create the classifier function
Use master
go
IF OBJECT_ID('DBO.CLASSIFIER_1','FN') IS NOT NULL
DROP FUNCTION DBO.CLASSIFIER_1
GO
 
-- note that this is just a regular (UDF) User Defined Function
CREATE FUNCTION DBO.CLASSIFIER_1()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
                DECLARE @val varchar(32)
                SET @val = 'default';
                if 'Sales_app' = SUSER_SNAME()
                                SET @val = 'Sales_Group';
                else if 'Reporting_app' = SUSER_SNAME()
                                SET @val = 'Reporting_Group';
                else if 'Fin_App' = SUSER_SNAME()
                                SET @val = 'Fin_Group';
                return @val;
END
GO
  
-- make function known to the Resource Governor
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = DBO.CLASSIFIER_1)
GO
  
--Check if Reconfigure is pending for any setting of Resource Governor
SELECT * FROM sys.dm_resource_governor_configuration
 
-- make the changes effective
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Workload – by different Sessions

declare @i int
declare @s varchar(100)
set @i = 10
while @i > 0
begin
                select @s = @@version;
end

Using Cap_CPU_Percent Parameter – New to SQL 2012 to configure Hard Cap on CPU

 CPU_CAP_PERCENT & AFFINITY SCHEDULER settings are available only through Scripts as of now- Planned to come in GUI from SP1

ALTER RESOURCE POOL [Pool_Sales_fin_app]
WITH (CAP_CPU_PERCENT=30)
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
  
ALTER RESOURCE POOL [Pool_Reporting]
WITH (CAP_CPU_PERCENT=40)
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Using Affinity Parameter to dedicate a scheduler to a single pool

ALTER RESOURCE POOL Pool_Sales_fin_app
WITH (AFFINITY SCHEDULER = (0))
GO
	 
ALTER RESOURCE POOL Pool_reporting
WITH (AFFINITY SCHEDULER = (1))
GO
	 
ALTER RESOURCE GOVERNOR RECONFIGURE
Go

Internal Tables\DMVs for Resource Governor

–Metadata Tables

SELECT * FROM sys.resource_governor_workload_groups
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_configuration

 DMV’s

SELECT * FROM sys.dm_resource_governor_workload_groups

SELECT * FROM sys.dm_resource_governor_resource_pools

SELECT * FROM sys.dm_resource_governor_configuration
 
Select * from sys.dm_resource_governor_resource_pool_affinity --- – New DMV in SQL 2012 for Affinity setting & Schedulers mask

Script to check which scheduler is being used by which Resource Group

select
      r.session_id,
      CONVERT(NCHAR(20), wg.name) as group_name,
      t.scheduler_id,
      r.status
from sys.dm_exec_requests r
      join sys.dm_os_tasks t on r.task_address = t.task_address
      join sys.dm_resource_governor_workload_groups wg on r.group_id = wg.group_id
where
      r.session_id > 50

Clean-Up

---Clean Up Script
ALTER RESOURCE GOVERNOR disable;
 
--Drop Logins and associated Users
USE [simple]
GO
DROP USER [Sales_app]
DROP USER [Reporting_app]
DROP USER [Fin_App]
GO
 
USE [master]
GO
DROP LOGIN [Sales_app]
DROP LOGIN [Reporting_app]
DROP LOGIN [Fin_App]
GO
 
--drop workload
USE [master]
GO
DROP WORKLOAD GROUP [Reporting_Group]
DROP WORKLOAD GROUP [Sales_Group]
DROP WORKLOAD GROUP [Fin_Group]
go
 
--drop pools
USE [master]
GO
DROP RESOURCE POOL [Pool_Sales_fin_app]
DROP RESOURCE POOL [Pool_Reporting]
GO
 
---drop classifier function
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = Null)
GO
DROP FUNCTION DBO.CLASSIFIER_1

Reference: Boris Baryshnikov’s blog post & Whitepapers for Resource Governor 2008 & 2012.

Regards

Sarabpreet Anand

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook