You are here:Home » tsql » configure Resource governer in SQL Server 2008

configure Resource governer in SQL Server 2008

---------------------------------------------------
-- Script to configure Resource governer
---------------------------------------------------


-- To Disable Resource Governer

Use Master
Go
ALTER RESOURCE GOVERNOR DISABLE;
Go

-- To enable Resource Governer

Use Master
Go
ALTER RESOURCE GOVERNOR RECONFIGURE
Go

---
--Resource pool to be used by OLTP Application
CREATE RESOURCE POOL OLTPPool
WITH
(
MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO
--Resource pool to be used by Report Application
CREATE RESOURCE POOL ReportPool
WITH
(
MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO


--Workload Group to be used by OLTP Application
CREATE WORKLOAD GROUP OLTPGroup
USING OLTPPool ;
GO
--Workload Group to be used by Report Application
CREATE WORKLOAD GROUP ReportGroup
USING ReportPool ;
GO


--CREATE CLASSIFIER FUNCTION to bifurcate the request

USE master;
GO
CREATE FUNCTION dbo.ResourceClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF (SUSER_NAME() = 'OLTPUser')
SET @WorkloadGroup = 'OLTPGroup'
ELSE IF (SUSER_NAME() = 'ReportUser')
SET @WorkloadGroup = 'ReportGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO


--We can alo use below are the criteria to bifircate the request.

LOGINPROPERTY (DefaultLanguage, DefaultDatabase)

ORIGINAL_DB_NAME()

HOST_NAME(), APP_NAME() *

CONNECTIONPROPERTY() –IP address, protocol, etc.

[S]USER_[S]NAME()

IS_SRVROLEMEMBER(), IS_MEMBER()

intrinsic functions, DATEPART, GETDATE(), etc.