Pool:
A resource pool, or pool, is a collection of system resources such as memory or CPU; it represents a portion of the physical resources of the server.
Depending on its settings, a pool may have a fixed size (its minimum and maximum resource usage settings are equal to each other) or have a part which is shared between multiple pools
(its minimum is less than its effective maximum). "Shared" in this case simply means that resources go to the pool that requests the resources first. In the default configuration all
resources are shared, thus maintaining backward compatibility with SQL Server 2005 policies. Two resource pools (internal and default) are created when SQL Server 2008 is installed.
Resource Governor also supports 18 user-defined resource pools. You specify MIN and MAX values for resources (CPU or Memory) which represents the minimum guaranteed resource availability
of the pool and the maximum size of the pool, respectively. The sum of MIN values across all pools cannot exceed 100 percent of the server resources. MAX value can be set anywhere in the
range between MIN and 100 percent inclusive. The internal pool represents the resources consumed by the SQL Server itself. This pool always contains only the internal group,
and the pool is not alterable in any way. Resource consumption by the internal pool is not restricted. Any workloads in the pool are considered critical for server function, and Resource Governor allows the internal pool to pressure other pools even if it means the violation of limits set for the other pools. The default pool is the first predefined user pool. Prior to any configuration the default pool only contains the default group. The default pool cannot be created or dropped but it can be altered. The default pool can contain user-defined groups in addition to the default group.
Group:
A workload group, or group, is a user-specified category of requests that are similar according to the classification rules that are applied to each session request. A group defines the policies for its members. A resource pool is assigned to a Workload Group, which is in turn is assigned to the Resource Governor. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed, apart from that the Resource Governor also supports user-defined workload groups. The internal workload group is populated with requests that are for internal SQL Server use only. You cannot change the criteria used for routing these requests and you cannot classify requests into the internal workload group whereas requests are mapped to default workload group, if there is a classification failure, an attempt to map to a non-existent workload group and there is no criteria to classify. If the Resource Governor is disabled, all new connections are automatically classified into the default group and System-initiated requests are classified into the internal workload group.
Classification:
Classification is a set of user-written rules that enable Resource Governor to classify session requests into the workload groups as described previously; for example classifying on the basis of user, application etc. It is implemented through a scalar Transact-SQL user-defined function (UDF) which is designated as a "classifier UDF" for the Resource Governor in the master database. Only one user-defined function can be designated as a classifier at a time
First I will create two resource pools to be used by OLTP and Reporting application,
then I will create two workload groups which will categorize the request coming from these applications.
Working with Resource Governor
--Resource pool to be used by OLTP Application
CREATE RESOURCE POOL OLTPPool
WITH
(
MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO
--Resource pool to be used by Report Application
CREATE RESOURCE POOL ReportPool
WITH
(
MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO
--Workload Group to be used by OLTP Application
CREATE WORKLOAD GROUP OLTPGroup
USING OLTPPool ;
GO
--Workload Group to be used by Report Application
CREATE WORKLOAD GROUP ReportGroup
USING ReportPool ;
GO
Next I will create the classifier UDF to route incoming request to different workload groups and finally I will enable Resource Governor with ALTER RESOURCE GOVERNOR RECONFIGURE statement.
Assumption here is, the OLTP application uses "OLTPUser" login whereas reporting application uses "ReportUser" login.
USE master;
GO
CREATE FUNCTION dbo.ResourceClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
--Declare the variable to hold the value returned in sysname.
DECLARE @WorkloadGroup AS SYSNAME
--If the user login is 'OLTPUser', map the connection to the
--OLTPGroup workload group.
IF (SUSER_NAME() = 'OLTPUser')
SET @WorkloadGroup = 'OLTPGroup'
--If the user login is 'ReportUser', map the connection to
--the ReportGroup workload group.
ELSE IF (SUSER_NAME() = 'ReportUser')
SET @WorkloadGroup = 'ReportGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO
--Register the classifier user-defined function and update the
--the in-memory configuration.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.ResourceClassifier);
GO
--Enabling Resource Governor(By default when you install
--SQL Server, Resource Governor is disabled)
--It loads the stored configuration metadata into memory
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
--Disabling Resource Governor
ALTER RESOURCE GOVERNOR DISABLE
GO
--It resets statistics on all workload groups and resource pools.
ALTER RESOURCE GOVERNOR RESET STATISTICS
GO
Resource Governor's Catalog Views and Dynamic Management Views
There are three new Catalog Views and three new Dynamic Management Views introduced for Resource Governor.
sys.resource_governor_configuration - used to display the Resource Governor configuration as stored in metadata.
sys.resource_governor_resource_pools - used to display resource pool configuration as stored in metadata.
sys.resource_governor_workload_groups - used to display workload group configuration as stored in metadata.
sys.dm_resource_governor_configuration - used to get the current in-memory configuration state of Resource Governor
sys.dm_resource_governor_resource_pools - used to get the current resource pool state, the current configuration of resource pools, and resource pool statistics.
sys.dm_resource_governor_workload_groups - used to get the workload group statistics and the current in-memory configuration of the workload group.
A resource pool, or pool, is a collection of system resources such as memory or CPU; it represents a portion of the physical resources of the server.
Depending on its settings, a pool may have a fixed size (its minimum and maximum resource usage settings are equal to each other) or have a part which is shared between multiple pools
(its minimum is less than its effective maximum). "Shared" in this case simply means that resources go to the pool that requests the resources first. In the default configuration all
resources are shared, thus maintaining backward compatibility with SQL Server 2005 policies. Two resource pools (internal and default) are created when SQL Server 2008 is installed.
Resource Governor also supports 18 user-defined resource pools. You specify MIN and MAX values for resources (CPU or Memory) which represents the minimum guaranteed resource availability
of the pool and the maximum size of the pool, respectively. The sum of MIN values across all pools cannot exceed 100 percent of the server resources. MAX value can be set anywhere in the
range between MIN and 100 percent inclusive. The internal pool represents the resources consumed by the SQL Server itself. This pool always contains only the internal group,
and the pool is not alterable in any way. Resource consumption by the internal pool is not restricted. Any workloads in the pool are considered critical for server function, and Resource Governor allows the internal pool to pressure other pools even if it means the violation of limits set for the other pools. The default pool is the first predefined user pool. Prior to any configuration the default pool only contains the default group. The default pool cannot be created or dropped but it can be altered. The default pool can contain user-defined groups in addition to the default group.
Group:
A workload group, or group, is a user-specified category of requests that are similar according to the classification rules that are applied to each session request. A group defines the policies for its members. A resource pool is assigned to a Workload Group, which is in turn is assigned to the Resource Governor. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed, apart from that the Resource Governor also supports user-defined workload groups. The internal workload group is populated with requests that are for internal SQL Server use only. You cannot change the criteria used for routing these requests and you cannot classify requests into the internal workload group whereas requests are mapped to default workload group, if there is a classification failure, an attempt to map to a non-existent workload group and there is no criteria to classify. If the Resource Governor is disabled, all new connections are automatically classified into the default group and System-initiated requests are classified into the internal workload group.
Classification:
Classification is a set of user-written rules that enable Resource Governor to classify session requests into the workload groups as described previously; for example classifying on the basis of user, application etc. It is implemented through a scalar Transact-SQL user-defined function (UDF) which is designated as a "classifier UDF" for the Resource Governor in the master database. Only one user-defined function can be designated as a classifier at a time
First I will create two resource pools to be used by OLTP and Reporting application,
then I will create two workload groups which will categorize the request coming from these applications.
Working with Resource Governor
--Resource pool to be used by OLTP Application
CREATE RESOURCE POOL OLTPPool
WITH
(
MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO
--Resource pool to be used by Report Application
CREATE RESOURCE POOL ReportPool
WITH
(
MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO
--Workload Group to be used by OLTP Application
CREATE WORKLOAD GROUP OLTPGroup
USING OLTPPool ;
GO
--Workload Group to be used by Report Application
CREATE WORKLOAD GROUP ReportGroup
USING ReportPool ;
GO
Next I will create the classifier UDF to route incoming request to different workload groups and finally I will enable Resource Governor with ALTER RESOURCE GOVERNOR RECONFIGURE statement.
Assumption here is, the OLTP application uses "OLTPUser" login whereas reporting application uses "ReportUser" login.
USE master;
GO
CREATE FUNCTION dbo.ResourceClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
--Declare the variable to hold the value returned in sysname.
DECLARE @WorkloadGroup AS SYSNAME
--If the user login is 'OLTPUser', map the connection to the
--OLTPGroup workload group.
IF (SUSER_NAME() = 'OLTPUser')
SET @WorkloadGroup = 'OLTPGroup'
--If the user login is 'ReportUser', map the connection to
--the ReportGroup workload group.
ELSE IF (SUSER_NAME() = 'ReportUser')
SET @WorkloadGroup = 'ReportGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO
--Register the classifier user-defined function and update the
--the in-memory configuration.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.ResourceClassifier);
GO
--Enabling Resource Governor(By default when you install
--SQL Server, Resource Governor is disabled)
--It loads the stored configuration metadata into memory
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
--Disabling Resource Governor
ALTER RESOURCE GOVERNOR DISABLE
GO
--It resets statistics on all workload groups and resource pools.
ALTER RESOURCE GOVERNOR RESET STATISTICS
GO
Resource Governor's Catalog Views and Dynamic Management Views
There are three new Catalog Views and three new Dynamic Management Views introduced for Resource Governor.
sys.resource_governor_configuration - used to display the Resource Governor configuration as stored in metadata.
sys.resource_governor_resource_pools - used to display resource pool configuration as stored in metadata.
sys.resource_governor_workload_groups - used to display workload group configuration as stored in metadata.
sys.dm_resource_governor_configuration - used to get the current in-memory configuration state of Resource Governor
sys.dm_resource_governor_resource_pools - used to get the current resource pool state, the current configuration of resource pools, and resource pool statistics.
sys.dm_resource_governor_workload_groups - used to get the workload group statistics and the current in-memory configuration of the workload group.
No comments:
Post a Comment