Database Reference
In-Depth Information
It returns several interesting columns of information about the file. The most interesting things are the stall data,
which is the time that users are waiting on different I/O operations. First, io_stall_read_ms represents the amount of
time in milliseconds that users are waiting for reads. Then there is io_stall_write_ms, which shows you the amount of
time that write operations have had to wait on this file within the database. You can also look at the general number,
io_stall, which represents all waits on I/O for the file. To make these numbers meaningful, you get one more value,
sample_ms, which shows the amount of time measured. You can compare this value to the others to get a sense of the
degree that I/O issues are holding up your system. Further, you can narrow this down to a particular file so you know
what's slowing things down in the log or in a particular data file. This is an extremely useful measure for determining
the existence of an I/O bottleneck. It doesn't help that much to identify the particular bottleneck.
Sys.dm_os_wait_stats
This is a generally useful DMO that shows aggregate information about waits on the system. To determine whether
you have an I/O bottleneck, you can take advantage of this DMO by querying it like this:
SELECT *
FROM sys.dm_os_wait_stats AS dows
WHERE wait_type LIKE 'PAGEIOLATCH%';
What you're looking at are the various I/O latch operations that are causing waits to occur. Like with
sys.dm_io_virtual_status, you don't get a specific query from this DMO, but it does identify whether you have a
bottleneck in I/O. Like many of the performance counters, you can't simply look for a value here. You need to
compare the current values to a baseline value in order to arrive at your current situation.
The WHERE clause shown earlier uses PAGEIOLATCH%, but you should also look for waits related to other
I/O processes such as WRITELOG, LOGBUFFER, and ASYNC_IO_COMPLETION.
When you run this query, you get a count of the waits that have occurred as well as an aggregation of the total
wait time. You also get a max value for these waits so you know what the longest one was since it's possible that a
single wait could have caused the majority of the wait time.
Disk Bottleneck Resolutions
A few of the common disk bottleneck resolutions are as follows:
•
Optimizing application workload
•
Using a faster I/O path
•
Using a RAID array
•
Using a SAN system
•
Using Solid State Drives
•
Aligning disks properly
•
Using a battery-backed controller cache
•
Adding system memory
•
Creating multiple files and filegroups
•
Moving the log files to a separate physical drive
•
Using partitioned tables
I'll now walk you through each of these resolutions in turn.
