0 SQL 2014: Resource Governor IO Enhancements

From SQL2014, We can use resource governor to throttle the IO. Here are some limitation:

• Only for Database engine. Not for SSRS, SSIS and SSAS.
• No workload manger between SQL Server instance. That means it only can throttle its own instance.
• For short duration query, it may finish before bandwidth control comes in.
• Only for the user operation. Not for system operation, such as lazy writer.
• Can not ad IO thresholds to the infernal resource pool .

Enable resource POOL, Work group

-- Exploring Resource Governor IO 

-- Enable Resource Governor 

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

-- Resource Governor IO Demo

-- Create Resource Pools

CREATE RESOURCE POOL [dbaopsL3] WITH

(

    MIN_CPU_PERCENT = 0, -- how much must be assigned to this pool

    MAX_CPU_PERCENT = 100, -- how much would be assigned if possible (note, can be exceeded if no contention for resources)

    CAP_CPU_PERCENT = 100, -- cannot-be-exceeded maximum, useful for predictable billing

    AFFINITY SCHEDULER = AUTO,

    MIN_MEMORY_PERCENT = 0, -- memory allocated to this pool that cannot be shared

    MAX_MEMORY_PERCENT = 100, -- percentage total server memory which is allowed to be used by this pool

    MIN_IOPS_PER_VOLUME = 0, -- minimum number of I/O operations per second per disk volume to reserve

    MAX_IOPS_PER_VOLUME = 5000 -- maximum. Note, 2147483647 is the max allowed value for this property.

);

GO

CREATE RESOURCE POOL [dbaopsL2] WITH

(

    MIN_CPU_PERCENT = 0, -- how much must be assigned to this pool

    MAX_CPU_PERCENT = 100, -- how much would be assigned if possible (note, can be exceeded if no contention for resources)

    CAP_CPU_PERCENT = 100, -- cannot-be-exceeded maximum, useful for predictable billing

    AFFINITY SCHEDULER = AUTO,

    MIN_MEMORY_PERCENT = 0, -- memory allocated to this pool that cannot be shared

    MAX_MEMORY_PERCENT = 100, -- percentage total server memory which is allowed to be used by this pool

    MIN_IOPS_PER_VOLUME = 0, -- minimum number of I/O operations per second per disk volume to reserve

    MAX_IOPS_PER_VOLUME = 50 -- maximum. Note, 2147483647 is the max allowed value for this property.

)

GO

--DROP RESOURCE POOL      [dbaopsL2] 

--DROP WORKLOAD GROUP   [dbaopsL2Group]

-- Create Workload Groups tied to resource pool

-- Create a Workload Group for [dbaopsL3Group]

CREATE WORKLOAD GROUP [dbaopsL3Group]

WITH (

    IMPORTANCE = MEDIUM, -- relative importance compared to other workgroups

    REQUEST_MAX_MEMORY_GRANT_PERCENT = 50, -- how much memory a single process can request from the pool

    REQUEST_MAX_CPU_TIME_SEC = 0, -- how long a single request can take without generating a CPU Threshold Exceeded event , 0 means unlimited       

    MAX_DOP = 0, -- max degree of parallelism allowed

    GROUP_MAX_REQUESTS = 0 -- num simultaneous events allowed, 0 means unlimited

)

USING [dbaopsL3]

GO

-- Create a Workload Group for [[dbaopsL2Group]]

CREATE WORKLOAD GROUP [dbaopsL2Group]

WITH (

    IMPORTANCE = MEDIUM, -- relative importance compared to other workgroups

    REQUEST_MAX_MEMORY_GRANT_PERCENT = 50, -- how much memory a single process can request from the pool

    REQUEST_MAX_CPU_TIME_SEC = 0, -- how long a single request can take without generating a CPU Threshold Exceeded event , 0 means unlimited       

    MAX_DOP = 0, -- max degree of parallelism allowed

    GROUP_MAX_REQUESTS = 0 -- num simultaneous events allowed, 0 means unlimited

)

USING [dbaopsL2]

GO

-- update resource governor to changes

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

Create the function and add to the resource governor

-- 3. Create a Classifier Function for Resource GOvernor

use master;

GO

CREATE FUNCTION fx_DBAClassifierFunction()

RETURNS sysname    WITH SCHEMABINDING

AS

BEGIN

    DECLARE @wg sysname

        IF SUSER_NAME() like '%dbal3%'

        SET @wg = 'dbaopsL3Group'

    ELSE IF SUSER_NAME() like '%dbal2%'

        SET @wg = 'dbaopsL2Group'

    ELSE 

        SET @wg = 'Default'

    RETURN @wg;

END;

GO

-- 4. Tell the resource governor to use the function

ALTER RESOURCE GOVERNOR WITH

(

    CLASSIFIER_FUNCTION = dbo.fx_DBAClassifierFunction

)

GO

-- Reconfigure resource GOvernor to take effect

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

Check various meta data

--- Show metadata

 MASTER

ect * from sys.dm_resource_governor_workload_groups;

ect * from sys.dm_resource_governor_configuration  ;

ect * from sys.dm_resource_governor_resource_pools;

ect * from sys.dm_resource_governor_resource_pool_volumes;

ect * from sys.dm_resource_governor_resource_pool_affinity;

ect g.name from sys.dm_exec_sessions s INNER JOIN sys.dm_resource_governor_workload_groups g

     ON s.group_id=g.group_id where s.session_id = @@SPID;

ECT  s.group_id,

     CAST(g.name AS NVARCHAR(20)) Name,

     s.session_id,

     s.login_time,

     CAST(s.host_name AS NVARCHAR(20)) HostName,

     CAST(s.program_name AS NVARCHAR(20)) ProgramName,

     rgp.read_io_stall_total_ms,

     rgp.read_io_stall_queued_ms

M    sys.dm_exec_sessions s

     INNER JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = s.group_id

     INNER JOIN sys.dm_resource_governor_resource_pools rgp ON rgp.pool_id = g.pool_id

RE   s.group_id > 2

ER BY g.name

Perform Counter for the resource pool