IO Stalls by Database File: Find the File to Move

IO stalls measure how long SQL Server waited for the disk. A file with high total stall and high wait per IO is the first candidate for a faster drive. One view reports them for every file of every database. The skill is reading the numbers without being fooled by them.

What a Stall Is

Every read or write that a database file asks of the disk has a clock. The time from the request to the answer is the stall. SQL Server adds the stalls per file and keeps separate totals for reads and writes. The view sys.dm_io_virtual_file_stats shows them, with the number of reads and writes behind each total.

The counters start at zero when the instance starts. A reading is therefore a total since then, not a rate. A file with a huge total can still be healthy, because it has been busy for months. Take a total and divide it by the number of IOs, or measure a window as the next section does.

Measure a Workload With Two Snapshots

The demo database is IoStallDemo. The first script creates it and takes a snapshot of its two files into a temporary table. The snapshot is the starting line for reading IO stalls over a window. Run the scripts on a test server.

IF DB_ID(N'IoStallDemo') IS NULL CREATE DATABASE IoStallDemo;
GO
USE IoStallDemo;
GO
DROP TABLE IF EXISTS dbo.Events;
CREATE TABLE dbo.Events (EventID int IDENTITY(1,1) PRIMARY KEY, Payload char(500) NOT NULL DEFAULT 'x');
GO
DROP TABLE IF EXISTS #FileBefore;
SELECT fs.file_id, fs.num_of_reads, fs.io_stall_read_ms, fs.num_of_writes, fs.io_stall_write_ms
INTO #FileBefore
FROM sys.dm_io_virtual_file_stats(DB_ID(N'IoStallDemo'), NULL) AS fs;

Now run a workload. The script inserts 60,000 rows and forces a checkpoint, so the data pages reach the file.

INSERT INTO dbo.Events (Payload) SELECT TOP (60000) 'x' FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CHECKPOINT;

The second snapshot is the live view. Subtract the first one, and rank the files by total stall. The last column divides the write stall by the number of writes.

SELECT mf.name AS FileName, mf.type_desc AS FileType,
       fs.num_of_reads - b.num_of_reads AS Reads,
       fs.io_stall_read_ms - b.io_stall_read_ms AS ReadStallMs,
       fs.num_of_writes - b.num_of_writes AS Writes,
       fs.io_stall_write_ms - b.io_stall_write_ms AS WriteStallMs,
       (fs.io_stall_read_ms - b.io_stall_read_ms) + (fs.io_stall_write_ms - b.io_stall_write_ms) AS TotalStallMs,
       CAST(1.0 * (fs.io_stall_write_ms - b.io_stall_write_ms) / NULLIF(fs.num_of_writes - b.num_of_writes, 0) AS decimal(9,2)) AS MsPerWrite
FROM sys.dm_io_virtual_file_stats(DB_ID(N'IoStallDemo'), NULL) AS fs
JOIN #FileBefore AS b ON b.file_id = fs.file_id
JOIN sys.master_files AS mf ON mf.database_id = fs.database_id AND mf.file_id = fs.file_id
ORDER BY TotalStallMs DESC;

In this run, the log file collected the most stall, 58 ms. Its 712 writes cost less than a tenth of a millisecond each. The data file stalled 33 ms over 112 writes, about 0.27 ms each. In another run the data file led, with 149 ms and 1.27 ms per write. Sample more than once. The totals decide where the time went. The wait per IO decides whether the drive is slow. Both numbers must be high before a file deserves a new drive.

List the Busiest Files of the Instance

To rank every file on the server, drop the database filter. This query adds the stall per IO, the creation size and the drive letter.

SELECT TOP (5) DB_NAME(fs.database_id) AS DatabaseName, mf.name AS FileName, mf.type_desc,
       fs.io_stall AS TotalStallMs, fs.num_of_reads + fs.num_of_writes AS TotalIos,
       CAST(1.0 * fs.io_stall / NULLIF(fs.num_of_reads + fs.num_of_writes, 0) AS decimal(9,2)) AS MsPerIo,
       mf.size / 128 AS SizeMB, LEFT(mf.physical_name, 3) AS Drive
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
JOIN sys.master_files AS mf ON mf.database_id = fs.database_id AND mf.file_id = fs.file_id
ORDER BY fs.io_stall DESC;

Shortly after a restart, a demo database and the system databases lead the list on the test server. Before that restart, five tempdb files led it. Each had about 142,000 to 150,000 ms of stall, at about 2 ms per IO. Your list will differ. One trap showed up in that earlier run. The catalog reported 8 MB for those tempdb files, their creation size, although the files were larger. Read the size from the database itself before you plan a copy.

Decide Which File to Move

Sort by total stall first, then check the wait per IO. A file with a large total and a low wait per IO is busy, not slow. A faster drive gains little. A file with a high wait per IO is the one the disk can’t serve. As a rule of thumb, data reads above about 20 ms deserve attention. That is a prompt, not a limit.

Look at the type as well. A log file writes in a straight line, so it wants low latency. A data file needs fast random reads. A log file waits on every commit, so a drive of its own can matter more for it.

The old way to move a file was to detach the database and attach it again. Today you point the file at the new path with ALTER DATABASE. Then you take the database offline, copy the file and bring it back online.

The order of the path change and the offline step can differ. See Rename Physical File Name of a SQL Server Database, which takes the database offline first. Copying to a new drive is the extra step here. Give the service account rights on the new folder.

You could argue that total stall is a poor measure, because a busy file always wins. That’s fair. It is why the wait per IO sits next to it. Run both queries on a normal day and again on a bad day, and compare the two reports.

What to Remember

IO stalls are totals since the restart. Read IO stalls per file, and measure a window with two snapshots when a problem is live. Rank by total stall, confirm with the wait per IO, and read the drive letter. Move the file that is both busy and slow.

Check the size and the path in the database itself. Keep the old file until the new one has run for a day. When you finish with the demo, drop the demo database.

USE master;
GO
IF DB_ID(N'IoStallDemo') IS NOT NULL
BEGIN
    ALTER DATABASE IoStallDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE IoStallDemo;
END;

A stall is not a verdict on the disk, it is a clue about where to look next.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.