Saturday, October 11, 2014

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
 

image

Perform Counter for the resource pool

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates