Classification

      Classifications are the internal rules that classify the incoming requests and route them into a workload groups. This classification is based on set of user defined function called classifier function. This classifier function will be created in master database. Once DBA enables the resource governor on SQL Server Then each and every single session to the server will be evaluated by the classifier function. It can evaluate various login properties.

• USER_NAME
• HOST_NAME
• WORKSTATION_NAME
• DB_NAME
• SUSER_SNAME

Read More

Workload Groups

             Workload groups are logical entities that represent one or more client workloads. Workload groups act as a container which accepts sessions from SQL Server users based on classification criteria applied to each requests. 

                As in resource pools there are also two predefined workload groups named Internal and Default. Internal workload group cannot be changed but can monitor it to see how SQL Server is utilizing memory and CPU.

Incoming requests to the server are classified in to Default workload group when there is no classification criteria applied for it or there was an attempt made to classify the request in to nonexistent workload group or there is failure in classification. This default workload group is user configurable.

• CREATE WORKLOAD GROUP group_name
  USING pool_name
  GO
   

Option	Description	Default
IMPORTANCE	Importance between workload groups using the same resource pool.	MEDIUM
REQUEST_MAX_MEMORY_GRANT_PERCENT	Max memory grant from the resource pool for query execution.	25%
REQUEST_MEMORY_GRANT_TIMEOUT_SEC	How long to wait for a query execution memory grant.	0
REQUEST_MAX_CPU_TIME_SEC	Max amount of CPU  time a request can use	0
MAX_DOP	Max degree of parallelism.	0
GROUP_MAX_REQUESTS	Maximum concurrent requests per group.	0

Read More

Resource Pools

               SQL Server Resource Governor introduced resource pools as a fundamental implementation of resource isolation within SQL Server instance. It represents the physical resource of the server. SQL Server Resource Pools are controlled with T-SQL and other management interfaces.

                   As a default when SQL Server is installed there are two resource pools named as Internal and Default. Internal resource pool basically represents the resources that are consumed by SQL Server itself for its running.(Resources that are reserved for SQL Server system tools). Default pool predefined user pool which contains default group. The workload runs by default on this resource pool. Therefore this Default resource pool is user configurable, but it can’t be Dropped or Create. Internal Resource pool is cant be changed.(can’t altered)

Create resource pools 

• CREATE RESOURCE POOL pool_name
  GO
  ALTER RESOURCE GOVERNOR RECONFIGURE
  GO        

Alter resource pools

• ALTER RESOURCE POOL  pool_name
  WITH (Option =value)
  GO
  ALTER RESOURCE GOVERNOR RECONFIGURE
  GO  

     

Option	Default	Internal
MIN_CPU_PERCENT	0	0
MAX_CPU_PERCENT	100	100
MIN_MEMORY_PERCENT	0	0
MAX_MEMORY_PERCENT	100	100
AFFINITY SCHEDULER	-	-
CAP_CPU_PERCENT	-	-

Read More

Resource Governor in SQL Server II

              Resource governor is technology that introduced in Microsoft SQL Server 2008 Enterprise edition onwards.  This feature can use to manage SQL Server workload and system resource consumption. It provide multi tenancy and resource isolation for single SQL Server instance when serving multi client workloads.

                SQL Server Resource governor allows you to set limits on the amount of memory and CPU resources that incoming requests can use and provide a way to isolate. It limits runaway quires adding fine grained resource tracking for charge back and delivering predictable performance.

               With SQL Server 2012 DBA’s can provide more complete isolation of CPU resources for workloads and put CAP’s on CPU usage for higher level of predictability and control greater proportion of SQL Server memory manager.

Resource governor issues comes with when there is unpredicted workload execution .

1. Long running Transact-SQL queries 
• SET NOCOUNT ON
  DECLARE @i INT
  DECLARE @s VARCHAR(100)
  SET @i = 100000000
  WHILE @i > 0
  BEGIN
  SELECT @s = @@version;
  SET @i = @i - 1;
  END
   
2. Database backups being performed
3. Non ending  query execution
• SET NOCOUNT ON
  DECLARE @i INT
  DECLARE @s VARCHAR(100)
  SET @i = 1
  WHILE @i > 0
  BEGIN
  SELECT @s = @@version;
  SET @i = @i + 1;
  END
   

 

Read More