You would have frequently experienced that whenever few queries run, they tend to slow down the whole server and hence have an impact on the business as the other applications do not respond in a timely manner. This hugely happens because there could be some type of queries which need a lot of IO operations to happen for communicating with the disks and hence that puts a huge constraint on the disk which results in ever increasing IO queues. There are other queries which go into the wait state as they are waiting for the IO sub-system to complete the request of the IO intensive queries which had started before.
Microsoft had introduced a new feature called "Resource Governor" since SQL Server 2008 which helped to control the utilization of specific resources (CPU and Memory) by different types of query groups. This feature is made available in only the Enterprise, Developer, and Evaluation editions of SQL Server. Now, as part of SQL Server 2014, Microsoft has enhanced this feature and added another type of resource which could be well controlled and that's the IO sub system. This is a major functionality released by Microsoft and its usage is going to be big as administrators have always wanted something to control the amount of IOs that could be taken by certain types of queries so that they do not bog down the whole server.
Enabling and Implementing IO Utilization using Resource Governor:
1.) Enable Resource Governor by right clicking on “Resource Governor” from Management folder of SSMS Object Explorer and selecting “Enable”
2.) Create a resource Pool for IO which consists of MIN and MAX IOPS for it
USE master;
go
CREATE resource pool restrictediopool
WITH ( max_iops_per_volume = 30, min_iops_per_volume = 1 );
go
3.) Create a resource Group which could be directed to the above resource pool and hence have a controlled IO utilization. By default there is a “Default” group which contains all the types of queries and an “Internal” group which is used by SQL Server for its internal processes.
USE master;
go
CREATE workload GROUP restrictediogroup using restrictediopool;
go
4.) Create a Resource classifier function to group the queries into Resource Groups. This classifier function will always be called whenever a query enters the system and it would be classified into a particular resource group. So this feature does have a bit of negative performance overhead with it. In this example I check that every query that comes into the system under the Login of “Backups” will go into the newly created group. I will make sure that every resource intensive query which I want to govern will be forced to run under this particular login:
USE master;
go
CREATE FUNCTION dbo.Restrictediousage()
returns SYSNAME
WITH schemabinding
AS
BEGIN
DECLARE @GroupName SYSNAME
IF Suser_name() = 'Backups'
BEGIN
SET @GroupName = 'RestrictedIOGroup'
END
ELSE
BEGIN
SET @GroupName = 'default'
END
RETURN @GroupName;
END
go
5.) Once the above components are created, we need to map the Resource Governor to the above created Classification function:
USE master;
GO
ALTER resource governor WITH (classifier_function = dbo.restrictediousage);
ALTER resource governor RECONFIGURE;
Demo to check the IOPS counter when we run a resource intensive operation
I will run a consistency check operation of the AdventureWorks database and see how many IOPS are getting generated for this operation as I will be running this under the login “Backups” and hence it would automatically be sent to the RestrictedIOGroup which in turn would be directed to the RestrictedIOPool
DBCC CHECKDB(AdventureWorks2012)
Add the following counters from the Perfmon window to view the specific Counters of MSSQL$SQL2014_Instance_Name:Resource Pool Stats Object:
1.) Disk Read IO/Sec - Default Instance
2.) Disk Read IO/Sec - RestrictedIOpool Instance
Here you can clearly see that the max IOPS is always around 30 as we had set initially to be the max resource for this particular pool. In this way any query under this pool would not take the complete IO for its request and hence other queries would be able to process simultaneously:
Now let us increase the MAX IOPS for this Resource pool and see how it behaves:
ALTER resource pool restrictediopool WITH (max_iops_per_volume=300);
ALTER resource governor RECONFIGURE;go
Here we can now see that the IOPS is going well beyond 30IOPS to as much as required to satisfy the request without any upper bound:
This is a very valuable feature released by Microsoft which should help the administrators to well control their IO subsystems between different type of user groups or other type of groups.
