SQL Server Storage and I/O Performance

Big Idea: SQL Server is built on storage. Even a “perfect” query can be slow if it must read too many pages from disk,
if the storage is slow, or if the log can’t flush fast enough. A DBA must learn to separate:
query inefficiency (too many reads) from storage inefficiency (reads are slow).

Recommended Class Duration

120–180 minutes (concepts + measurement + labs)

Prerequisites

• Basic performance tuning concepts (CPU/memory/waits)
• Basic understanding of indexes and logical reads
• Optional: basic OS/storage terminology (SSD/HDD, RAID, SAN)

Key concepts students must understand

• Logical read: reading a page from memory (buffer pool). Still “work,” but usually much faster than disk.
• Physical read: reading a page from disk because it is not in memory.
• Read-ahead: SQL Server may prefetch pages into memory for scans to improve throughput.
• Cache: if the working set fits in memory, repeated queries can be very fast.

Key teaching point: Many performance problems are not “disk is slow.” They are “query reads too much.”
If you reduce logical reads, you reduce physical I/O pressure.

Data file I/O (MDF/NDF)

• Mostly random reads (especially OLTP) + some writes from checkpoints.
• Workload driven by queries: scans, seeks, lookups, sorts, hashes (spills).
• Performance depends on latency + throughput + caching.

Log file I/O (LDF)

• Mostly sequential writes (log records must be flushed to disk for commits).
• Write-heavy workloads depend on log write latency.
• Even if data file storage is fast, slow log flush can slow every transaction.

Key teaching point: If transactions are slow and wait on log flush, your query tuning might not help.
You must check log I/O and write patterns.

• Latency (ms): how long each read/write takes. (Often the most important factor for OLTP.)
• IOPS: how many I/O operations per second the storage can handle.
• Throughput (MB/s): how much data per second can be transferred.
• Queue depth: how many I/Os are waiting. High queue can signal saturation.

Key teaching point: High throughput can still feel “slow” if latency is high.
OLTP systems usually suffer first from latency, not bandwidth.

• PAGEIOLATCH_*: waiting for a data page to be read from disk into memory (data file read latency).
• WRITELOG: waiting for transaction log to flush to disk (log write latency).
• IO_COMPLETION: OS-level I/O completion waits (can appear with heavy I/O).
• ASYNC_IO_COMPLETION: often seen with backup/restore operations or heavy async I/O patterns.
• TEMPDB-related pressure: can manifest as I/O + latch waits when tempdb is under contention.

Key teaching point: Waits show where time went; you must still identify which queries are generating the I/O.

Script 1 — File latency by database file (data + log)

/* File latency (ms) by database file */
SELECT
  DB_NAME(vfs.database_id) AS database_name,
  mf.type_desc AS file_type,
  mf.name AS logical_name,
  mf.physical_name,
  vfs.num_of_reads,
  vfs.num_of_writes,
  vfs.io_stall_read_ms,
  vfs.io_stall_write_ms,
  CAST(vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads,0) AS decimal(18,2)) AS avg_read_latency_ms,
  CAST(vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes,0) AS decimal(18,2)) AS avg_write_latency_ms,
  CAST((vfs.io_stall_read_ms + vfs.io_stall_write_ms) /
       NULLIF((vfs.num_of_reads + vfs.num_of_writes),0) AS decimal(18,2)) AS avg_total_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
  ON vfs.database_id = mf.database_id
 AND vfs.file_id = mf.file_id
ORDER BY avg_total_latency_ms DESC;

How to interpret: High avg_read_latency_ms suggests slow data reads (PAGEIOLATCH).
High avg_write_latency_ms on log files suggests log flush pressure (WRITELOG).
Always compare to your baseline and workload type.

Script 2 — Top cached queries by logical reads (find “who is reading too much”)

/* Top cached queries by total logical reads */
SELECT TOP (25)
  qs.total_logical_reads AS total_logical_reads,
  (qs.total_logical_reads / NULLIF(qs.execution_count,0)) AS avg_logical_reads,
  qs.execution_count,
  (qs.total_elapsed_time / NULLIF(qs.execution_count,0)) AS avg_elapsed_time,
  DB_NAME(st.dbid) AS database_name,
  LEFT(st.text, 4000) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_logical_reads DESC;

How to interpret: High logical reads usually means scans, missing indexes, poor filtering, or inefficient joins.
Reducing logical reads is often the best I/O optimization.

Common causes of TempDB pain

• Large sorts/hashes (query spills) because memory grants are insufficient.
• Heavy use of temp tables by ETL or reporting.
• Row versioning (snapshot isolation / RCSI) under heavy update workloads.
• TempDB on slow storage or competing with data/log on the same disk.

Key teaching point: Some I/O problems are actually “query plan problems” that spill to TempDB.
Fixing the query can remove the storage pressure.

Fix Category A: Reduce reads (most common best win)

• Add the right index (not too many indexes, but the right ones).
• Rewrite queries to be SARGable (avoid functions on indexed columns).
• Return fewer rows (filter earlier, avoid SELECT *).
• Fix poor join strategies (avoid massive scans when not needed).

Fix Category B: Reduce writes (especially log pressure)

• Batch large modifications (avoid one massive transaction).
• Avoid unnecessary index maintenance that creates excessive log activity.
• Use appropriate recovery model and log backup frequency (for your business requirements).
• Keep transactions short (reduces logging duration + blocking risk).

Fix Category C: Improve storage design (when needed)

• Separate data files, log files, and TempDB when possible (reduces contention).
• Pre-size files and set reasonable growth to avoid constant autogrowth events.
• Use faster storage tiers for log and TempDB if write-heavy workloads exist.
• Validate storage latency with OS-level metrics (not only SQL Server views).

Key teaching point: If you only “upgrade disks” without reducing logical reads, you will buy time but not solve root causes.

Student Submission Template (Copy/Paste)

• Top 3 files by latency: (database, file type, avg read/write latency)
• Worst file is: data/log + reason
• Top 5 queries by logical reads:
• Hypothesis: why do these queries read so much?
• One proposed fix:

Prompt:
“What would you investigate first if users complain that ‘saving orders is slow’ but reporting queries are fine?
Which file type (data vs log) would you suspect and why?”