24. Resource Governor

• Resource Pool
  • internal pool - instance
  • default pool
  • default external pool MLS
• Workload Group
• Classifier Function with parameters
  • username
  • role membership
  • application name
  • login property
  • connection property
  • time
  • etc.
• Max 64 resource pools per instance
• Max memory is a hard limit
• Max CPU is a soft limit, can be a hard limit

CREATE RESOURCE POOL <pool>
  CAP_CPU_PERCENT x /* hard limit */
  MAX_CPU_PERCENT y /* soft limit */

IOPS only managed if MAX set:

• only affects
• writes are done by instance
• not size of IOPS

CREATE EXTERNAL RESOURCE POOL ...;

CREATE RESOURCE POOL <app_pool> WITH (...);

ALTER RESOURCE GOVERNOR RECONFIGURE;

Memory grant % 0 => blocks SORT or HASH JOIN

Error 8657, DOP = 1 and not enough RAM

CREATE WORKLOAD GROUP <grp>
  WITH (...)
  USING <app_pool>;
ALTER RESOURCE GOVERNOR RECONFIGURE;

One classifier function per instance - returns SYSNAME. Must reside in Master database:

USE MASTER;
CREATE FUNCTION dbo.classifer
  RETURNS SYSNAME
  WITH SCHEMA BINDING
  AS BEGIN
    USER_NAME()
    HOST_NAME()
    ...

ALTER RESOURCE GOVERNOR
  WITH (CLASSIFIER_FUNCTION=dbo.classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

EXECUTE AS LOGIN='...';

REVERT;

Performance

MSQSQL$[INSTANCE,NAME]
  Resource Pool Stats
  Workload Group Stats

sys.dm_resource_governor_resource_pools
sys.dm_resource_governor_workload_groups