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
0 comments:
Post a Comment