An Introduction to SQL Server I/O Performance Monitoring

Optimal SQL Server performance relies on the I/O subsystem. For example before a transaction can commit the I/O subsystem has to send an acknowledgment that  all the associated  log records have been flushed to disk. Similarly to service transactions SQL Server  will  read pages from disk into memory and then at a later stage write them back out gain to meet recovery intervals.

 

 

When working with SQL Server the primarily measurements I use for I/O are:

 

  • IOPS – The number of disk Input/Output operations per second
  • Latency – The time taken to complete an I/O request
  • Queue Length – The number of outstanding I/O requests
  • IO Size – The size of each I/O request
  • Bandwidth – The rate of bytes sent and retrieved from disk

 

The tools we use to measure I/O dictate the level of granularity and accuracy we bring to performance analysis.

There is a trade-off in the time it takes to  analyse the performance data and we also have to take into account anyoverhead that may be experienced when running collection routines. The key is to understand from experience what we need to collect for any given scenario and only go to this level. Having said that  the primary tools I use when working with SQL Server at the OS level are:

 

  • Perfmon
  • SQL Server DMVs
  • Extended Events / ETW

1 (1)

 

 

In the figure above taking an example of a  simple SQL Server file layout (2 data and 1 log file) we can see how each tool can be used to collect an ever more granular level of information.

 

Using Perfmon  we can collect data at the volume level, it is easy to configure and it has built-in analysis. This may be sufficient for a lot of scenarios especially when there is a 1-1 relationship between the SQL Server files and the disk volumes.

But what about the scenario where we have multiple files on the same volume?

What about when we want to look at the response times for a particular operation in SQL Server and the IO size associated with it?

 

In the table below I have taken the primary I/O measurements & tools described the level of granularity and plotted the various counters, dmv’s and traces associated with them.

 

4

Summary

In this post we looked at the measurements I use to monitor SQL Server I/O performance. I explained how we can use various tools to collect performance metrics. Understanding what  we need to collect and then using the appropriate tools to collect this information is key to successful performance analysis

References

http://blogs.technet.com/b/askcore/archive/2012/03/16/windows-performance-monitor-disk-counters-explained.aspx

http://technet.microsoft.com/en-us/library/cc938959.aspx

http://blogs.msdn.com/b/psssql/archive/2012/01/23/how-it-works-sys-dm-io-pending-io-requests.aspx

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/03/14/dissecting-sql-server-physical-reads-with-extended-events-and-process-monitor.aspx

http://msdn.microsoft.com/en-us/library/ff878413.aspx

http://blogs.technet.com/b/robertsmith/archive/2012/02/07/analyzing-storage-performance-using-the-windows-performance-toolkit.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s