One of the more common challenges DBAs face when requesting new SAN environments from storage administrators is being able to gather the actual number of I/O operations per second that SQL Server actually performs. See your storage admins always speak in terms of performance in terms of IOPs, latency and throughput, whereas database folks like to be able to speak in terms of transactions per second. So in the past it was a challenge for the DBA to communicate with the SAN admin about IOPs requirements—it involved carefully capturing a lot of different perfmon counters, and making sure they all added up correctly. It was definitely more art than science.
Introducing Resource Governor in SQL Server 2014
One of the key feature enhancements to SQL 2014 was the addition of I/O control to resource governor. Resource governor is an enterprise edition feature that allows DBAs to manage workloads by classifying connections that have specific amounts of hardware resources allocated to them. When this feature was introduced in SQL 2008, it had one missing element—the ability to constrain workloads based on I/O. Especially since CPUs have gotten much faster since 2008, I/O has become the bottleneck for many, many systems. So, in SQL 2014, Resource Governor can manage IOPs per volume (note, it’s not in the GUI as of now, you’ll have to use T-SQL to configure it).
I have Standard Edition—How Does This Help Me?
Well, if you have standard edition, you won’t be able to use resource governor to manage resources. However, the perfmon counters from resource governor still come with your install. And this is how you can measure IOPs from SQL.
Figure 1 Perfmon Counters
That handy SQL Server:Resource Pool Stats counter and it’s Disk Read IO/sec and Disk Write IO/sec provide you with the data you need to give your SAN admin. In this screenshot this is an Enterprise Edition instance, and you can see my resource pools on the left side—so if you are using resource governor, you could use this to classify IO workload by application for potential chargeback situations.
Good luck and happy SAN tuning.
