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.

1 comment:

  1. Hello

    I need a complete example described step by step through the creation
    and use of
    SQLServer 2008 Resource Governor to MAXDOP = 1 in one or more database.
    Example of the database name: XYZ.

    The Hilary has something to send as described above?

    Thanks.

    Best regards
    José Júlio Duarte

    ReplyDelete