How Do I Measure IOPs from SQL Server?

Published On: 2015-12-11By:

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.



Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link