Resource Governor is a feature
which can manage SQL Server Workload and System Resource Consumption. We can
limit the amount of CPU and memory consumption by limiting /governing
/throttling on the SQL Server.
If there are different workloads
running on SQL Server and each of the workload needs different resources or
when workloads are competing for resources with each other and affecting the
performance of the whole server resource governor is a very important task.
For example, if two users are
running queries parallel among them one user query should run an high
priority then the ideal solution for this is resource governor.
SQL Server have two by default
created resource governor component.
1) Internal –This is used by
database engine exclusives and user have no control.
2) Default – This is used by all the
workloads which are not assigned to any other group.
What are the major components of the
resource governor?
- Resource Pools
- Workload Groups
- Classification
We can configure the resource
governor using below process
- Create resource pool
- Create a workload group
- Create classification function based on the
criteria specified
- Enable Resource Governor with classification
function
Step 1: Creating
Resource Pool
We are creating two resource pools.
1) High_PriorityPool and 2) Low_PriorityPool
-----------------------------------------------
-- Step 1: Create Resource Pool
-----------------------------------------------
-- Creating Resource Pool for
high priority users
CREATE RESOURCE POOL Low_PriorityPool
WITH
( MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=30,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO
-- Creating Resource Pool for Low
Priority users
CREATE RESOURCE POOL High_PriorityPool
WITH
( MIN_CPU_PERCENT=50,
MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50,
MAX_MEMORY_PERCENT=100)
|
Note: We can change the above values as per
our requirement.
Step 2: Creating Workload Group
We are creating two workloads each
mapping to each of the resource pool which we have just created.
-----------------------------------------------
-- Step 2: Create Workload
Group
-----------------------------------------------
-- Creating Workload Group for High
priority pool
CREATE WORKLOAD GROUP HighPriorityGroup
WITH
(IMPORTANCE = HIGH, MAX DOP =8)
USING High_PriorityPool;
GO
-- Creating Workload Group for Low
priority pool
CREATE WORKLOAD GROUP LowPriorityGroup
WITH
(IMPORTANCE = LOW, MAX DOP =0)
USING Low_PriorityPool;
GO
|
|
Step 3: Creating user
defined function which routes the workload to the appropriate workload group.
In this example we are
checking SUSER_NAME() and making the decision of Workgroup selection. We
can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER() etc.
-----------------------------------------------
-- Step 3: Create UDF to Route
Workload Group
-----------------------------------------------
CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'HighPriorityUser')
SET @WorkloadGroup = ' HighPriorityGroup'
ELSE IF (SUSER_NAME() = 'LowPriorityUser')
SET @WorkloadGroup = 'LowPriorityGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO
Note : Users which are not
mapped to any work load group will go to the default work load group.
|
Step 4: In this final
step we enable the resource governor with the classifier function created in
earlier step 3.
-----------------------------------------------
-- Step 4: Enable Resource
Governer
-- with UDFClassifier
-----------------------------------------------
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
|
Below will
show how resource governor will process the workloads at a time.
To Verify the requests that are sent to different pools use below script :
SELECT
rpool.name
as PoolName,
COALESCE(SUM(rgroup.total_request_count), 0) as TotalRequest,
COALESCE(SUM(rgroup.total_cpu_usage_ms), 0) as TotalCPUinMS,
CASE
WHEN
SUM(rgroup.total_request_count)
> 0 THEN
SUM(rgroup.total_cpu_usage_ms) / SUM(rgroup.total_request_count)
ELSE
0
END
as AvgCPUinMS
FROM
sys.dm_resource_governor_resource_pools
AS rpool
LEFT OUTER JOIN
sys.dm_resource_governor_workload_groups AS rgroup
ON
rpool.pool_id
= rgroup.pool_id
GROUP BY
rpool.name;
Below is the example for classifier function :
CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'BOADMINUSR')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = 'Bobjrepusr')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = 'Dataocean_Redwood')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[Infa_SFDC]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[Infasfdc]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[info_do]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[informatica]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[Infouser]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[intg_otm]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[Intg_SIOP_INFO]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[Intg_Vannet_INFO]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[iwetlusr]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[iwusr]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[lot_attr]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[realtimeuserbissas]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[Redwood_User]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[sapecc_do_fin]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[SSAS_ANALYTICS]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[sa]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[ssas01_connect]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[TaskEDWAuditTst]')
SET @WorkloadGroup = ' HighPriorityGroup'
IF(SUSER_NAME() = '[VWRI\Informatica]')
SET @WorkloadGroup = ' HighPriorityGroup'
ELSE IF (SUSER_NAME() like 'VWRI\%')
SET @WorkloadGroup = 'LowPriorityGroup'
ELSE IF (SUSER_NAME() like 'NT SERVICE\%')
SET @WorkloadGroup = 'LowPriorityGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO
No comments:
Post a Comment