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