In Part2 we discussed WORKLOAD GROUP in detail and traverse through possible number of constants we can use. Using the knowledge we will create workload with more then one resource and see how in conjunction we can limit the resource usage.

Let's start creating a workload using more then one resource

CREATE WORKLOAD GROUP grpDev
WITH (
      GROUP_MAX_REQUESTS = 50,
         IMPORTANCE = MEDIUM,
            REQUEST_MAX_MEMORY_GRANT_PERCENT = 30,
               MAX_DOP = 1
   )

Next create the function so that whenever the RG runs it will call this function to retrieve our group name.

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 the RG aware of this function

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = ResGovMgr)

Don't forget to reconfigure the RG;

ALTER RESOURCE GOVERNOR RECONFIGURE

Will be back with more on Resource Governor.

Bandagi!