About Resource Govrnor

• ajsql990

  SSC Journeyman

  Points: 90

  More actions

  March 5, 2014 at 11:43 am

  #1694443

  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