Measure IOPs from SQL Server

Measure IOPs from SQL Server

Investigating  performance problems, We may need to look at I/O stats as well. Find how the  Monitoring  I/O on storage system is performing.  Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on the server

Disk Reads/Sec + Disk Writes/Sec = IOPS

Disk Reads Bytes/Sec + Disk Writes Bytes/Sec = Throughput

Download SQLIO from Microsoft – Check  how fast the storage really is using SQLIO

Example will come soon:

Throttling the IOPS using resource governor to reduce the storage spike:

Resource Governor:

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

Microsoft introduced two new resource pool options that allow you to control the I/O threshold setting.  These two settings are: MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME.  Each of these parameters can be set to a value between zero (0) and 2,147,483,647.  If the parameter MIN_IOPS_PER_VOLUME is set to zero(0) it means that there is no minimum threshold for I/Os.  If the MAX_IOPS_PER_VOLUME is set to zero(0) this means that the  upper bounds of I/Os per second is unlimited. If these parameters are set to a number then that is the minimum or maximum number of I/O operations per second that resource governor will allow a disk volume to have. 

On a busy IO instance where you want to make sure your process gets all the IO it needs to perform well you can use the MIN_IOPS_PER_VOLUME setting.  By setting this parameter to number of the minimum IOs your process needs, SQL Server will make sure your process is allowed that number of IOs per second.  If you have other processes that you don’t want to use all the I/O bandwidth then you can throttle those processes by setting the maximum number of IOs by using the MAX_IOPS_PER_VOLUME setting.

This will help to throtal the checkDB and index maintenance IO usage.