Preventing Application to create performance problem using Resource Governor in a Shared SQL Server Environment
Section A (Theory)
One of the common performance problem in a shared SQL Server environment is multiple applications competing for resources with in the same SQL Server Instance. While all applications are not equally important from business perspective (ie. some application may have higher priority over others while some other application/s may be simply resource starved and creates problem for others causing performance problem to higher priority applications), it was not very easy to separate them in their own silo with in a same SQL Server Instance. However this problem can be largely resolved with proper use of Resource Governor which is available from SQL 2008 on-wards.Please refer to http://technet.microsoft.com/en-us/library/bb934084(v=sql.105).aspx to learn more about Resource Governor.
In this particular post we will explore how we can configure a
solution using Resource Governor to ensure that we can safely share a SQL
Server Instance to host multiple application without creating performance
problem. To achieve this objective we basically need to executive below steps sequentially:
- We need prioritize all the applications who are sharing the SQL Server and then take a decision how much resource each of them will be allowed through Resource Governor. This step is most complex and need involvement with Business Users. However please note that even if we configure applications subject to a certain quota of CPU and Memory, this does not mean that those application will not be able to consume more CPU and Memory than their defined quota. Actually they will be able restricted only when SQL Server is busy and needs to guarantee resources to a Higher Priority Application (ie. who has a higher quota).
- We need to identify how each applications connect SQL Server and write a classifier function accordingly. Most commonly each application will have their own login to SQL Server (It can be a SQL or Windows Service Login).
- Once we have all details from Step#1 and 2, we will configure Resource Governor to implement the solution.
In this section, we will configure Resource Governor on a SQL
Server 2008 R2 Enterprise Edition. We have two applications here named Unicorn
(application login id is unicorn_applogin) and Bluecube (application login id
is <domainname>\bluecube_service. Also we will assume Bluecube is more
critical than Unicorn and hence will implement below design to manage the performance
of share environment:
BlueCube will have a quota of 90% CPU and 90% Memory.
Unicorn will have a quote of 30% CPU and 30% Memory.
Please use following steps to complete the configuration:
First ensure that you are connected to correct instance. This is very important step while you are working on a prod instance.
Then create Resource Pools for each applications. You need to actually create pool for each type of application here (based on the resource quote they will have). Multiple application can share a pool as long as they have same resource allocation quota.
Now create the Resource Groups for each Pool.
Now complete the configuration using Classifier Function.
At this point on-wards, any connections coming to SQL Instance
will be screened by Resource Governor and will be de-prioritized if needed
ensuring vital application does not experience performance issue.
As DBA, you can always check which SPID is using which Resource Group to ensure
that the configuration is working as expected. Please refer to the below shot
to visualize how our solution implemented in this Demo Section is working.
Section C
(Appendix)
Please find the codes used in Section B (Demo) for your quick reference:
select @@servername
Create RESOURCE POOL POOL_UNICORN
WITH
(
MIN_CPU_PERCENT = 1, MAX_CPU_PERCENT=30,
MIN_MEMORY_PERCENT = 1, MAX_MEMORY_PERCENT = 30
);
GO
Create RESOURCE POOL POOL_Blue
WITH
(
MIN_CPU_PERCENT = 1, MAX_CPU_PERCENT=90,
MIN_MEMORY_PERCENT = 1, MAX_MEMORY_PERCENT = 90
);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
CREATE WORKLOAD GROUP [GROUP_UNICORN] WITH(group_max_requests=0,
importance=Low,
request_max_cpu_time_sec=600,
request_max_memory_grant_percent=30,
request_memory_grant_timeout_sec=0,
max_dop=0) USING [POOL_UNICORN]
GO
CREATE WORKLOAD GROUP [GROUP_BLUE] WITH(group_max_requests=0,
importance=Low,
request_max_cpu_time_sec=6000,
request_max_memory_grant_percent=90,
request_memory_grant_timeout_sec=0,
max_dop=0) USING [POOL_Blue]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
CREATE FUNCTION RG_ClassifierApp () RETURNS SYSNAME WITH
SCHEMABINDING
AS
BEGIN
DECLARE @workload_group sysname;
IF (suser_sname() LIKE 'unicorn_applogin')
SET @workload_group = 'GROUP_UNICORN';
IF (suser_sname() LIKE 'USOLN\bluecube_service')
SET @workload_group = 'GROUP_BLUE';
RETURN @workload_group;
END;
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.RG_ClassifierApp);
ALTER RESOURCE GOVERNOR RECONFIGURE
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id,
s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS
nvarchar(20)), cast (s.login_name as nvarchar(20))
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
ORDER BY g.name
GO
Comments