To understand how the Resource Governor works let’s try to work with this feature
To Resource Governor everything is a workload, and we are going to create a group on to which we would like to limit a resource, so this group would become a workload group for Resource Governor – does it make sense?
CREATE WORKLOAD GROUP grpDeveloper
Now let’s create a function which will return our workload group name every time when Resource Governor runs and limits the resource
CREATE FUNCTION ResGovMgr () RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grpName AS SYSNAME
IF (APP_NAME = 'MANAGEMENT STUDIO’)
SET grpName = 'grpDeveloper'
RETURN grpDeveloper
END
Now let’s make the resource governor aware of our function
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ResGovMgr)
In order to limit the resource for our workload group we have to apply the limit changes to the workload group earlier created. Here the resource is CPU and the limit value is 30%
ALTER WORKLOAD GROUP grpDeveloper
WITH (MAX_CPU_PERCENT = 30)
And finally we make the in-memory resource governor aware of our changes
ALTER RESOURCE GOVERNOR RECONFIGURE
Now once you configure your workload and add to the resource governor, your work is done – let the Resource Governor handles the resource. In case if the workload group tries to utilize more the percentage allocated the Resource Governor either trigger some event or stop the victim process.
Bandagi!