IO LATCH in SQL Server

IO LATCH waits occur when SQL Server is waiting for a data page to be read from disk into memory.

When a page is not in Buffer Pool:

Ø  Query requests the page
Ø  SQL Server issues Physical I/O
Ø  Thread waits → IO Latch wait recorded
Ø  Page loaded into memory 

Ø  Latch released and query resumes

Common IO LATCH Wait Types

Wait Type	Meaning
PAGEIOLATCH_SH	Waiting to read page for shared read
PAGEIOLATCH_EX	Waiting to read page for modification
PAGEIOLATCH_UP	Waiting to read upgradeable page
ASYNC_IO_COMPLETION	Waiting for async file IO
WRITE_LOG	Log file write delay (related to log I/O)

 Difference between IO LATCH vs PAGELATCH

IO Concept	PAGELATCH	IO LATCH
Location	In memory already	Needs to be fetched from disk
Reason	Memory concurrency protection	Disk speed / latency
Type	Internal synchronization	Disk wait
Common in	TempDB contention	Slow storage
Fix	Reduce contention	Improve I/O

 PAGELATCH = CPU + concurrency in memory

IO LATCH = Slow Disk / I/O latency

To see the io latch we will use below query

SELECT wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%PAGEIO%' ORDER BY wait_time_ms DESC;

Below query is used to identify the object.

SELECT     wt.wait_type,     wt.resource_description,     r.command,     DB_NAME(r.database_id) DBName,     r.status,     r.wait_type,     r.wait_time FROM sys.dm_os_waiting_tasks wt JOIN sys.dm_exec_requests r ON wt.session_id = r.session_id WHERE wt.wait_type LIKE '%PAGEIO%';

 

Below are some steps to fix IO LATCH Issues

Ø  Reduce Physical IO

o   Add indexes

o   Avoid scans

o   Tune queries

Ø  Avoid Bad Code Patterns

o   SELECT *


o   Scalar functions in WHERE

o   Implicit conversions

o   Non-SARGable queries

Ø  Read Committed Snapshot

o   Helps reduce blocking

Ø  Fix TempDB

o   Multiple files

o   SSD

o   Optimized sizing