Today, we will go through four queries that are related to storage. We start out with Query #23, which is Volume Info. This query retrieves information from the sys.master_files catalog view and the sys.dm_os_volume_stats dynamic management function about any operating system volumes or mount points on your server where you have any SQL Server database files. Query #23 is shown in Figure 1.
Figure 1: Query #23 Volume Info
This query shows you how large each operating system volume or mount point is, and how much space is available. You don’t ever want to run out of space on a volume where SQL Server database files are located (for obvious reasons), but many people are not aware that being low on space can affect the performance of the volume, with both magnetic storage and with flash storage.
With magnetic storage, being low on disk space means that you may be no longer getting the increased disk performance possible from short-stroking. This can affect your disk performance with magnetic drives because the arm that moves the drive heads back and forth to read the data has to move further as an individual disk becomes more and more full, plus the disk platters are moving slower on the inside portion compared to the outside portion of the disk.
The reason why I say that this only “may” be affecting your disk performance is because with many SANs, whatever space has been allocated for a volume may be spread across all of the available drives in the SAN, so the fact that your volume is low on space may have very little to do with what is happening with the overall SAN.
With flash storage, as the drive gets lower on space, it becomes more difficult for garbage collection and TRIM operations to complete without affecting performance. Generally speaking, your write performance will tend to decrease and become more inconsistent as the drive is closer to being full of data.
Query #24 is Drive Level Latency. This query retrieves information from the sys.master_files catalog view and the sys.dm_io_virtual_file_stats dynamic management function about I/O statistics for SQL Server data and log files. Query #24 is shown in Figure 2.
Figure 2: Query #24 Drive Level Latency
This particular query aggregates all of the file activity for your database data and log files up to the volume level. This activity includes your normal workload, plus anything else (such as index maintenance, database backups, log reader activity for things like replication, database mirroring, and AlwaysOn AGs) that touches your database data and log files.
These numbers are cumulative since SQL Server was last started. This means that the latency numbers you see here will usually tend to be a little higher than what you might see in Performance Monitor, and also higher than what shows up at the SAN-level. This query helps you understand your disk performance for SQL Server at the volume level. Once you understand this, then you are ready to drill down to the individual database file level with the next query.
Query #25 is IO Stalls by File. This query retrieves information from the sys.master_files catalog view and the sys.dm_io_virtual_file_stats dynamic management function about I/O statistics for SQL Server data and log files. Query #25 is shown in Figure 3.
Figure 3: Query #25 IO Stalls by File
This query is similar to the previous query, with all of the same caveats. This query goes down to the individual database file level rather than rolling up the data to the volume level. Once you see high read or write latency for a particular volume, you will want to then determine which database file(s) are seeing the highest latency on that volume. This will help you decide what you might be able to do to improve the situation.
Query #26 is IO Warnings. This query reads the five most recent SQL Server Error logs to look for 15 second I/O warnings. Query #26 is shown in Figure 4.
Figure 4: Query #26 IO Warnings
This query may take some time to complete if your SQL Server Error log files are very large. Finding any 15 second I/O warnings is very strong evidence that you were seeing very poor storage subsystem performance when they were recorded. The warnings go down to the database file level, so you can see which drive and which file was affected.
What you want to look for is whether there is any sort of recognizable pattern to when you are getting these warnings. For example, perhaps you often see a series of 15 second warnings for the data files for one database at around 3AM each morning. Perhaps this is when you are doing index maintenance, plus running DBCC CHECKDB at the same time, or some other repeating pattern of scheduled disk intensive activity.
On the other hand, it may be the case that you see many 15 second I/O warnings at random times, spread across many drive volumes and disk files. This would be a pretty good indicator of generally poor storage subsystem performance!
The results of these four queries are very useful to have available when you talk to your storage administrator about disk performance. The more evidence and metrics you have available about what SQL Server is seeing from a storage perspective, the stronger your case will be.