What is Resource Governance and how to create a Resource Governance in SQL SERVER

In This post we are going to see what is Resource Governance and how to create a Resource Governance in SQL SERVER.

SQL Server provides a new feature known as Resource Governor, which allows the database administrators to put a check on the server resources consumed by various connections or applications. It can control the CPU time and memory used by specific applications. It allows you to allocate the server resources to different types of connections. It category the incoming connections to different workloads groups, Each of which can be monitored individually. WorkLoad group are contained inside the resource pool which contains the portion of CPU and memory resources available to the current instance. It allows you to set importance for different workload groups.

o   Creating a Resource Pool

o   Creating a workload group

o   Creating a classifier function

o   Configure the Resource governor.

-- Create a Resource Pool

CREATE RESOURCE POOL reportpool

WITH (MAX_CPU_PERCENT = 50, MAX_MEMORY_PERCENT = 50)

-- Create a WorkLoad Group

CREATE WORKLOAD GROUP reportworkload

WITH (IMPORTANCE = HIGH)

USING samplepool

-- Create a Classifier Function

CREATE FUNCTION reportfunc()

RETURNS sysname

WITH schemabinding

AS

BEGIN

   DECLARE @wklgrp sysname

   IF(APP_NAME()= 'Report application')

   SET @wklgrp = 'reportworkload'

   ELSE

   RETURN default

  

   RETURN @wklgrp

END

-- Classifier the Resource Governer

ALTER RESOURCE GOVERNOR

WITH ( CLASSIFIER_FUNCTION = dbo.reportfunc)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

From this post you can learn the details about the resource Governance very well and become expertise to create a Resource Governance in SQL SERVER