Nov 122012
 

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

Leave a Reply