Reads and Writes per File: Rank Files by Bytes Moved

Reads and writes per file show which database file SQL Server uses the most. One view keeps a running tally for every data file and log file. A short query turns that tally into a ranked list. Then you know which file deserves a faster drive before you move anything.

Where the Tally Lives

The view is sys.dm_io_virtual_file_stats. It returns one row per file with the number of reads and writes and the bytes moved. The counters start at zero when the instance starts or when the database is opened. They only grow, so a row shows the total since then. The view replaces the older function fn_virtualfilestats, which returns the same counts under other column names.

The view reports by file number, not by name. Join it to sys.master_files to see the logical name and the file type. The view needs VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

Build a Database With Three Data Files

The demo database has four files: a primary file, one for current orders, one for the archive, and a log. Each data file sits in its own filegroup, so each table lands in a known file. The script reads the default data and log folders from the server. It needs about 200 MB of free disk space, and the cleanup removes the files.

DECLARE @dir nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @log nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultLogPath'));
DECLARE @sql nvarchar(max) = N'CREATE DATABASE FileUsageDemo ON PRIMARY (NAME = N''FileUsageDemo'', FILENAME = N''' + @dir + N'FileUsageDemo.mdf'', SIZE = 32MB), FILEGROUP OrdersFG (NAME = N''FileUsageOrders'', FILENAME = N''' + @dir + N'FileUsageOrders.ndf'', SIZE = 64MB), FILEGROUP ArchiveFG (NAME = N''FileUsageArchive'', FILENAME = N''' + @dir + N'FileUsageArchive.ndf'', SIZE = 64MB) LOG ON (NAME = N''FileUsageDemo_log'', FILENAME = N''' + @log + N'FileUsageDemo_log.ldf'', SIZE = 32MB);';
IF DB_ID(N'FileUsageDemo') IS NULL EXEC (@sql);
GO
USE FileUsageDemo;
GO
CREATE TABLE dbo.Orders (OrderID int IDENTITY(1,1) PRIMARY KEY, CustomerID int NOT NULL, Note char(200) NOT NULL DEFAULT 'standard order') ON OrdersFG;
CREATE TABLE dbo.OrderArchive (OrderID int NOT NULL PRIMARY KEY, CustomerID int NOT NULL, Note char(200) NOT NULL) ON ArchiveFG;
INSERT INTO dbo.Orders (CustomerID) SELECT TOP (100000) ABS(CHECKSUM(NEWID())) % 500 FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
GO
CHECKPOINT;

Give Each File a Different Job

Taking a database offline and back online resets its counters and empties its cached pages. That gives the demo a clean start. The workload reads the orders table twice and copies 60,000 rows into the archive. The orders file should collect the reads, and the archive file the writes. Never run the offline step on a database that people use.

USE master;
ALTER DATABASE FileUsageDemo SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE FileUsageDemo SET ONLINE;
GO
USE FileUsageDemo;
GO
SELECT COUNT(*) AS Orders FROM dbo.Orders;
SELECT COUNT(*) AS Orders FROM dbo.Orders;
INSERT INTO dbo.OrderArchive (OrderID, CustomerID, Note) SELECT OrderID, CustomerID, Note FROM dbo.Orders WHERE OrderID <= 60000;
CHECKPOINT;

Rank the Files

The query below joins the view to the file list and adds the megabytes read and written. It also computes each file as a share of the database total. Reads and writes per file are best ranked by bytes moved, which counts big transfers and small ones fairly.

SELECT mf.name AS LogicalName, mf.type_desc AS FileType, vfs.num_of_reads AS Reads, vfs.num_of_writes AS Writes,
       CAST(vfs.num_of_bytes_read / 1048576.0 AS decimal(9, 1)) AS ReadMB,
       CAST(vfs.num_of_bytes_written / 1048576.0 AS decimal(9, 1)) AS WrittenMB,
       CAST(100.0 * (vfs.num_of_bytes_read + vfs.num_of_bytes_written) / SUM(vfs.num_of_bytes_read + vfs.num_of_bytes_written) OVER () AS decimal(5, 1)) AS SharePercent
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.master_files AS mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY vfs.num_of_bytes_read + vfs.num_of_bytes_written DESC;

This is one run, and your counts will differ a little. The pattern will not. The orders file ranks first with 527 of the 587 reads and no writes. The log file ranks second with the most write calls, 350 of them, but small ones. The archive file received fewer, larger writes. The primary file barely moved, which is what a file holding only system tables should do.

Read the share column with care. A file with 43 percent of the traffic is not a problem. It is only the busiest. A busy file on a fast drive is fine, so judge speed separately. The post Database File Latency: Read and Write Stalls per IO covers that side.

Check the Whole Instance

Drop the database filter and the same view ranks reads and writes per file across the server. The list starts with the files that moved the most bytes since the last restart. The block below does that: it passes NULL for the database and adds the database name.

SELECT TOP (5) DB_NAME(vfs.database_id) AS DatabaseName, mf.name AS LogicalName, mf.type_desc AS FileType,
       CAST((vfs.num_of_bytes_read + vfs.num_of_bytes_written) / 1048576.0 AS decimal(12, 1)) AS TotalMB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY vfs.num_of_bytes_read + vfs.num_of_bytes_written DESC;

The values depend on your server, so none are shown. Tempdb and system database files can lead this list. A user data file in that spot is the lead to follow.

Reads and writes are counted per call, and a call has no fixed size. The log file shows 350 writes for about 20 MB. The archive file shows 33 writes for about 13 MB. Compare the megabyte columns to see where the load sits.

What the Tally Cannot Tell You

The counters cover the whole time since the database opened. One night of heavy work can hide a quiet day. To catch a busy hour, read the view twice and subtract. The counters also include maintenance such as backups and integrity checks. And a file that moves many bytes is not slow, only used.

You could argue that Windows Performance Monitor already shows disk activity. It does, but per drive. When two files share a drive, the drive counter blurs them, and the SQL Server view still tells them apart.

What to Remember

Use sys.dm_io_virtual_file_stats joined to sys.master_files to rank reads and writes per file. Rank by bytes moved, read the share, and remember that the tally runs from the last restart or database open. Judge speed with a separate latency check before you move a file. When you finish, drop the demo database.

USE master;
GO
ALTER DATABASE FileUsageDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE FileUsageDemo;

A busy file is not a slow file, it is only the one the server visits most.

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