Troubleshooting SQL Server IO Performance/Bottleneck

Updated on October 18, 2022     Kisan Patel

How to resolve disk IO performance problems that are a very important point for SQL Server troubleshooting.

Find Average read latency:

SELECT [database_id],
  	  DB_NAME([database_id]) AS [database_nm],
	[file_id],
	[num_of_reads],
	[io_stall_read_ms],
	1.*[io_stall_read_ms]/[num_of_reads] as avg_read_latency,
	[num_of_bytes_read],
	[num_of_writes],
	[num_of_bytes_written],
	[io_stall_write_ms],
	[io_stall],
	[size_on_disk_bytes]
FROM sys.[dm_io_virtual_file_stats](NULL, NULL)
ORDER BY [io_stall_read_ms] DESC;

Source: https://www.sqlshack.com/sql-server-troubleshooting-disk-i-o-problems/

Avg Disk Sec per Read:

Source: https://www.mssqltips.com/sqlservertip/2460/perfmon-counters-to-identify-sql-server-disk-bottlenecks/

Most Expensive IO Queries:

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

SELECT q.[query_hash],
SUBSTRING(t.[text], (q.[statement_start_offset] / 2) * 1,
	((CASE q.[statement_end_offset]
		WHEN -1 THEN DATALENGTH(t.[text])
		ELSE q.[statement_end_offset]
	END - q.[statement_start_offset]) / 2) + 1),
SUM(q.[total_physical_reads]) AS [total_physical_reads]
FROM sys.[dm_exec_query_stats] AS q
CROSS APPLY sys.[dm_exec_sql_text](q.sql_handle) AS t
GROUP BY q.[query_hash],
SUBSTRING(t.[text], (q.[statement_start_offset] / 2) * 1,
	((CASE q.[statement_end_offset]
		WHEN -1 THEN DATALENGTH(t.[text])
		ELSE q.[statement_end_offset]
	END - q.[statement_start_offset]) / 2) + 1)
ORDER BY SUM(q.[total_physical_reads]) DESC;


SELECT p.[query_plan]
FROM sys.[dm_exec_query_stats] AS q
CROSS APPLY sys.[dm_exec_query_plan](q.[plan_handle]) AS p
WHERE q.[query_hash] = 'YOU HASH FROM ABOVE QUERY Ex. 0x1D28A11F23D389C7'

Source: https://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

PAGEIOLATCH Wait Type

-- PAGEIOLATCH waits

-- real time wait stats
SELECT *
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGEIOLATCH%'
GO

--historical wait stats
SELECT *
FROM sys.dm_os_wait_stats
--order by wait_time_ms_desc
WHERE wait_type LIKE 'PAGEIOLATCH_SH'

--clear wait stats
dbcc sqlperf ('sys.dm_os_wait_stats', clear)

Source: https://blog.sqlauthority.com/2011/02/09/sql-server-pageiolatch_dt-pageiolatch_ex-pageiolatch_kp-pageiolatch_sh-pageiolatch_up-wait-type-day-9-of-28/