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:
- SQL Server DMVs
- Extended Events / ETW
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.
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