I'm always looking for ways to measure SQL Server's performance and present the information in a readable and useful format. Here are some useful tips I've come across for getting and analyzing I/O information:
- Use sys.dm_io_virtual_file_stats.
- Use SQL Server Performance Monitor to help identify I/O bottlenecks.
- Understand the infamous error message.
Tip 1
A common way to measure SQL Server performance is to examine I/O statistics by using dynamic management functions (DMFs) and dynamic management views (DMVs), which were introduced in SQL Server 2005. These dynamic management objects let you obtain many different types of statistics about a system's state. With this information, you can monitor your system, tune its performance, and troubleshoot any problems that arise.
A particularly useful I/O-related DMF is sys.dm_io_virtual_file_stats, which returns I/O statistics for database files. This DMF's num_of_bytes_read and num_of_bytes_written columns let you easily calculate total I/O. In addition to calculating total I/O, you can use common table expressions (CTEs) to determine the percentage of I/O usage, which tells you where most of the I/O is occurring.
In the web-exclusive article "Querying DMFs to Analyze Performance Stats", Itzik Ben-Gan provides a helpful tutorial on how to use sys.dm_io_virtual_file_stats. He provides several scripts, including the one that Listing 1 shows. This script uses a CTE to return the I/O usage for each database across all drives, regardless of the disk layout of the particular database.
Listing 1: Itzik Ben-Gan's Code That Calculates the Percentage of I/O for Each Database |
 |
Determining I/O usage is useful in many scenarios. For example, if you have the opportunity to move files on one physical array to other physical arrays, you can determine which files have the highest I/O so that you can put them on different arrays.
However, I/O usage doesn't help much in determining whether or not a performance problem exists. For instance, suppose you determine the I/O usage of the files on a server. You notice that one file has an I/O usage of 90 percent. Sounds like a problem, right? Not necessarily. If a file is using 90 percent of the I/O but there's no waiting for reads or writes, you should be OK. The more users wait, the more performance is potentially affected. So, in this case, you also need to look at statistics that tell you how long users have to wait for reads and writes to occur. To do so, you can use the io_stall_read_ms and io_stall_write_ms columns in sys.dm_io_virtual_file_stats. These columns can tell you the total time that users waited for reads and writes to occur for a given file.