It’s time to get into the details on the new features of resource governor that is shipped with SQL Server 2014, like we mentioned in a previous article on SQL Server 2014 new features, this is a very useful setting that you can leverage to better control your disk usage.

The idea around resource governor is to give the DBA the power to set a threshold of usage to diverse resources like memory or cpu. Based on a function you will be able to define which transactions are going to fall under this threshold. So you can define something like “every time user XYZ executes a new transaction apply the threshold for memory”, this way every new transactions from the corresponding login will be capped and you will ensure it won’t consume more than the specified.

In previous SQL Server versions you were able to define this cap for certain resources like memory and cpu in the form of percentages, so for instance you could setup a threshold of cpu usage to up to 50% of the total cpu capacity, however you were not able to define a max or minimum value for your disk usage.

In SQL Server 2014 MS defined the ability to set min and max IOPS, which stands for Input and Output Operations Per Second, which is the sum of the number of read and writes per second (Disk Reads/sec + Disk Writes/sec).

So before we check on how to use this new feature, the first question we are going to answer is:

Which is my Current IOPS?

Getting to know this information is a must, because how will you be able to set a minimum or maximum IOPS if you don’t know what is your average or your highest IOPS reported for a period of time?

You need to define a baseline, which is a sample period of time that is good enough for you to define an average usage. It depends on your system, a normal system that has the same type of operations every week and there is nothing special by the end of the month, or biweekly, or during weekends, then probably 1 week is enough for a sample to be used as your baseline. However if you do experience higher loads by the end of the month, certain reports run biweekly and that type of thing, then you will need to come up with your own number based on your knowledge of system or business.

After you have identified your baseline, then basically you will need to monitor the IOPS for that period of time, also if you have a specific process, job or transaction that is troublesome you will need to monitor IOPS as well. With this information in hand then you will be able to easily define your min and max IOPS usage in resource governor for the said process.

HOW DO I MONITOR IOPS?

You can setup performance monitor, if you check the counter under SQL Server: Resource Pool Stats, you will be able to see both disk and reads IO/sec.

You can also resort to use sys.dm_io_virtual_file_stats. This DMV reset it’s information with every sql server restart, so is important that you check last sql server startup time with a query like:

[code language=”sql”]

SELECT login_time FROM sysprocesses WHERE spid = 1

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

[/code]

Based on this, you will know if the information gathered is good enough and if it matches your baseline, to be more clear about this, if your baseline is 1 week, and last sql server restart was over a week ago, then you should be fine. In this case the longer sql sever have been running the better.

With below query you will be able to know quickly the IOPS and BPS used by your databases per file.

[code language=”sql”]

SELECT DB_NAME(xIOPS.database_id) AS DatabaseName

, MF.type_desc AS FileType

, MF.name AS VirtualFileName

, MF.Physical_Name AS StorageLocation

, ROUND((num_of_reads + num_of_writes)/sample_ms,4) AS IOPS

, ROUND((num_of_bytes_read + num_of_bytes_written)/sample_ms,2) AS BPS

FROM sys.dm_io_virtual_file_stats(null,null) xIOPS

JOIN sys.master_files MF ON xIOPS.database_id = MF.database_id AND xIOPS.file_id = MF.file_id

ORDER BY DatabaseName ASC, VirtualFileName ASC

[/code]

With this information now you are able to proceed and setup your resource governor.

HOW TO USE RESOURCE GOVERNOR TO SET MAXIMUM AND MINIMUM VALUES FOR I/O

In this example you can start by creating a new user

[code language=”sql”]

CREATE LOGIN MyIOLogin WITH PASSWORD = ‘log0!’;

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [MyIOLogin]

GO

[/code]

Create the resource pool and workload group and give it the name you want:

[code language=”sql”]

CREATE RESOURCE POOL MyIOLogin;

GO

CREATE WORKLOAD GROUP MyIOLogin USING MyIOLogin;

GO

[/code]

Create the classifier function that will route the incoming transactions to the respective workload groups and pools. The function will filter anything using MyIOLogin to a group of the same name and anything else to the default pool

[code language=”sql”]

USE MASTER;

GO

CREATE FUNCTION dbo.Restrict_IO()

RETURNS SYSNAME WITH SCHEMABINDING

AS

BEGIN

DECLARE @GroupName SYSNAME

IF SUSER_NAME() = ‘MyIOLogin’

BEGIN

SET @GroupName = ‘MyIOLogin’

END

ELSE

BEGIN

SET @GroupName = ‘default’

END

RETURN @GroupName;

END

[/code]

Then assign the classifier to Resource Governor.

[code language=”sql”]

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Restrict_IO);

[/code]

And then you are able to alter the pool and setup your min or max value as desired:

[code language=”sql”]

ALTER RESOURCE POOL MyIOLogin WITH (Max_IOPS_PER_VOLUME=600);

ALTER RESOURCE GOVERNOR RECONFIGURE;

[/code]

Conclusion

Using resource governor is now a more complete and powerful tool that you can use to set thresholds to most of your resources, give you the power to encapsulate certain processes and control SQL Server consumption in your server.