Understanding the "I/O by File" Page in SQL Server agent (4381192)

The I/O by File page in SQL Server monitoring tools (such as Foglight) provides comprehensive, file-level metrics about data and log files in your SQL Server instance. This page helps DBAs and system administrators analyze which files are handling the most operations, where bottlenecks might exist, and the overall health of database storage.

Metrics commonly displayed:

• Read Operations: Total read requests served for each file
• Write Operations: Total write requests served for each file
• MB Read / MB Written / MB Total: Amount of data read or written (in megabytes)
• Total Operations: Combined total of reads and writes
• Reads Wait Time / Writes Wait Time / Total Wait Time: Amount of time queries/processes waited for disk operations to complete
• Latency: How long (on average) I/O operations take to complete

Each metric represents a cumulative total, which can quickly grow high in busy environments.

How "I/O by File" Metrics Are Measured

SQL Server maintains counters for each file via DMVs such as `sys.dm_io_virtual_file_stats`, aggregating reads, writes, bytes, and wait times.

These metrics include all I/O:

• Direct Query Activity: Reads/writes from user SQL (SELECT, INSERT, UPDATE, DELETE)
• Background/System Operations: Includes checkpointing, log flushes, index maintenance, backups, growth events, or other engine processes
• Indirect/Parallel: Multiple queries, sessions, or threads may push operations to the same file

Counter Reset Behavior

When are values reset?

• The cumulative counters for read operations, write operations, MB read/written, waits, etc., are maintained by SQL Server itself.
• These metrics are reset ONLY when the SQL Server instance restarts. (Source: sys.dm_io_virtual_file_stats page on the Microsoft website, refer to the Remarks section) 
• Restarting the Foglight agent (or any external monitoring tool) does NOT reset these totals. The Foglight agent simply continues to collect and display the current values directly from SQL Server.
• If cumulative values suddenly drop or reset to zero, this almost always indicates a SQL Server restart event, not a monitoring agent event.

SQL PI Difference - Why Totals Don't Match

• SQL PI (Performance Investigator) shows activity per query or per statement, based on sampling intervals, not the file lifetime. The metrics are based on the interval range set in the zonar (time selection) at the top of the SQL PI page.
• Scope Limitation: SQL PI reports only the writes, reads, waits, or MBs directly attributed to individual SQL statements it tracks within each interval.
• Not All Activities Tracked: Internal/background SQL Server activities are usually NOT captured by SQL PI because they're not linked to a user query unless their collection is specifically enabled in the SQL PI properties.
• Result: The cumulative "I/O by File" totals will always be much higher than the max/aggregated values for any query or interval in SQL PI. This is expected and reflects different measurement approaches.

How to Use and Interpret "I/O by File" Metrics:

1. Identify Hot Files and Bottlenecks

• Files with high cumulative totals may indicate disks under stress; analyze their workload and check for storage subsystem issues.
• Use latency and wait time to find potential disk delays.

2. Correlate with Workload Trends

• Pair file-level trends with session or query analytics to see if changes in user activity match spikes in file operations.
• Investigate both direct SQL activity and periodic background jumps (e.g., after a nightly backup).

3. Monitor for Sudden Counter Resets

• If cumulative values drop to zero or suddenly shrink, check if the SQL Server instance was restarted.

4. Plan Capacity

• Use cumulative stats to forecast disk usage, set alert thresholds, and guide hardware upgrades.
• Combine with interval spikes to ensure your capacity plans cover both sustained and peak workloads.

Summary Table

Additional Advice

• High cumulative values for log/data files in busy databases are expected.
• If values reset unexpectedly, verify SQL Server uptime rather than focusing on monitoring agent status.
• Always use both file and query/session metrics for holistic performance troubleshooting.
• High log file write activity is expected in busy systems due to constant transaction logging.