About Resource Govrnor

  • I think you setup the resource pool wrong. Follow the below steps might help your situation.

    /*-----------------------------------------------

    Step 0: (Optional and for Demo Purpose)

    Create Two User Logins

    1) ReportUser, 2) PrimaryUser

    Use ReportUser login for Reports workload

    Use PrimaryUser login for OLTP workload

    -----------------------------------------------*/

    Step 1: Creating Resource Pool

    We are creating two resource pools. 1) Report Server and 2) Primary OLTP Server. We are giving only a few resources to the Report Server Pool as described in the scenario 1 the other server is mission critical and not the report server.

    -----------------------------------------------

    -- Step 1: Create Resource Pool

    -----------------------------------------------

    -- Creating Resource Pool for Report Server

    CREATE RESOURCE POOL ReportServerPool

    WITH

    ( MIN_CPU_PERCENT=0,

    MAX_CPU_PERCENT=30,

    MIN_MEMORY_PERCENT=0,

    MAX_MEMORY_PERCENT=30)

    GO

    -- Creating Resource Pool for OLTP Primary Server

    CREATE RESOURCE POOL PrimaryServerPool

    WITH

    ( MIN_CPU_PERCENT=50,

    MAX_CPU_PERCENT=100,

    MIN_MEMORY_PERCENT=50,

    MAX_MEMORY_PERCENT=100)

    GO

    Step 2: Creating Workload Group

    We are creating two workloads each mapping to each of the resource pool which we have just created.

    -----------------------------------------------

    -- Step 2: Create Workload Group

    -----------------------------------------------

    -- Creating Workload Group for Report Server

    CREATE WORKLOAD GROUP ReportServerGroup

    USING ReportServerPool ;

    GO

    -- Creating Workload Group for OLTP Primary Server

    CREATE WORKLOAD GROUP PrimaryServerGroup

    USING PrimaryServerPool ;

    GO

    Step 3: Creating user defined function which routes the workload to the appropriate workload group.

    In this example we are checking SUSER_NAME() and making the decision of Workgroup selection. We can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER() etc.

    -----------------------------------------------

    -- Step 3: Create UDF to Route Workload Group

    -----------------------------------------------

    CREATE FUNCTION dbo.UDFClassifier()

    RETURNS SYSNAME

    WITH SCHEMABINDING

    AS

    BEGIN

    DECLARE @WorkloadGroup AS SYSNAME

    IF(SUSER_NAME() = 'ReportUser')

    SET @WorkloadGroup = 'ReportServerGroup'

    ELSE IF (SUSER_NAME() = 'PrimaryUser')

    SET @WorkloadGroup = 'PrimaryServerGroup'

    ELSE

    SET @WorkloadGroup = 'default'

    RETURN @WorkloadGroup

    END

    GO

    Step 4: In this final step we enable the resource governor with the classifier function created in earlier step 3.

    -----------------------------------------------

    -- Step 4: Enable Resource Governer

    -- with UDFClassifier

    -----------------------------------------------

    ALTER RESOURCE GOVERNOR

    WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);

    GO

    ALTER RESOURCE GOVERNOR RECONFIGURE

    GO

    Step 5: If you are following this demo and want to clean up your example, you should run following script. Running them will disable your resource governor as well delete all the objects created so far.

    -----------------------------------------------

    -- Step 5: Clean Up

    -- Run only if you want to clean up everything

    -----------------------------------------------

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)

    GO

    ALTER RESOURCE GOVERNOR DISABLE

    GO

    DROP FUNCTION dbo.UDFClassifier

    GO

    DROP WORKLOAD GROUP ReportServerGroup

    GO

    DROP WORKLOAD GROUP PrimaryServerGroup

    GO

    DROP RESOURCE POOL ReportServerPool

    GO

    DROP RESOURCE POOL PrimaryServerPool

    GO

    ALTER RESOURCE GOVERNOR RECONFIGURE

    GO

    GOOD LUCK

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply