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

Sarabpreet Singh Anand

SQLServer - MVP Sarabpreet is SQLServer MVP, Consultant, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 11+ years of Experience and worked with Industry Leaders. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”.

You may also like...

Leave a Reply