Resource Governor enables to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests.
Available from SQL Server 2008.
Resource Governor creation steps and components:
RESOURCE POOL
CREATE RESOURCE POOL pool_name
[ WITH
( [ MIN_CPU_PERCENT =value ]
[ [ , ] MAX_CPU_PERCENT =value ]
[ [ , ] MIN_MEMORY_PERCENT =value ]
[ [ , ] MAX_MEMORY_PERCENT =value ] )
]
WORKLOAD GROUP
CREATE WORKLOAD GROUP group_name
[ WITH
( [ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
[ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT =value ]
[ [ , ] REQUEST_MAX_CPU_TIME_SEC =value ]
[ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC =value ]
[ [ , ] MAX_DOP =value ]
[ [ , ] GROUP_MAX_REQUESTS =value ] )
]
[ USING { pool_name | "default" } ]
CLASSIFIER FUNCTION
CREATE FUNCTION rgclassifier_v1() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name AS SYSNAME
......
RETURN @grp_name
END
Note: function must be in master DB!
Assign CLASSIFIER FUNCTION to RESOURCE GOVERNOR
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1)
Resources configuration to the WORKLOAD GROUP
ALTER WORKLOAD GROUP groupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30)
or
WITH (MAX_CPU_PERCENT = 50)
Activation
ALTER RESOURCE GOVERNOR RECONFIGURE
DMVs and catalog views to monitor RESOURCE GOVERNOR
SELECT * FROM sys.dm_resource_governor_workload_groups
SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_configuration
SELECT * FROM sys.resource_governor_workload_groups
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_configuration
Note:
Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_configuration
Note:
Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
Useful links:
Managing SQL Server Workloads with Resource Governor
Resource Governor Concepts
Good videos:
Good and short sumery:
Video, monitor and work from management studio:
Another articles:
No comments:
Post a Comment