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!