MSSQL Storage Diagnostics

Databases

SQL Server

MSSQL Diagnostics & Tuning

Diagnostic Scripts

SSMS Activity Monitor

Tables & Views

Bibliography

Windows Storage Diagnostics

First Responder Kit

Diagnostic Scripts

SELECT wait_type,

waiting_tasks_count,

wait_time_ms

FROM sys.dm_os_wait_stats

WHERE wait_type like 'PAGEIOLATCH%'

ORDER BY wait_type

wait_type waiting_tasks_count wait_time_ms------------------------------------------------------------ -------------------- --------------------PAGEIOLATCH_DT 0 0PAGEIOLATCH_EX 382 3938PAGEIOLATCH_KP 0 0PAGEIOLATCH_NL 0 0PAGEIOLATCH_SH 19408 65676PAGEIOLATCH_UP 4312 28131

SELECT database_id,

file_id,

io_stall,

io_pending_ms_ticks,

scheduler_address

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS t1,

sys.dm_io_pending_io_requests AS t2

WHERE t1.file_handle = t2.io_handle

SELECT DB_NAME(a.database_id) AS [Database Name] , b.name + N' [' + b.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + N']' AS [Logical File Name] , UPPER(SUBSTRING(b.physical_name, 1, 2)) AS [Drive] , CAST(( ( a.size_on_disk_bytes / 1024.0 ) / (1024.0*1024.0) ) AS DECIMAL(9,2)) AS [Size (GB)] , a.io_stall_read_ms AS [Total IO Read Stall] , a.num_of_reads AS [Total Reads] , CASE WHEN a.num_of_bytes_read > 0 THEN CAST(a.num_of_bytes_read/1024.0/1024.0/1024.0 AS NUMERIC(23,1)) ELSE 0 END AS [GB Read], CAST(a.io_stall_read_ms / ( 1.0 * a.num_of_reads ) AS INT) AS [Avg Read Stall (ms)] , CASE WHEN b.type = 0 THEN 30 /* data files */ WHEN b.type = 1 THEN 5 /* log files */ ELSE 0 END AS [Max Rec Read Stall Avg], a.io_stall_write_ms AS [Total IO Write Stall] , a.num_of_writes [Total Writes] , CASE WHEN a.num_of_bytes_written > 0 THEN CAST(a.num_of_bytes_written/1024.0/1024.0/1024.0 AS NUMERIC(23,1)) ELSE 0 END AS [GB Written], CAST(a.io_stall_write_ms / ( 1.0 * a.num_of_writes ) AS INT) AS [Avg Write Stall (ms)] , CASE WHEN b.type = 0 THEN 30 /* data files */ WHEN b.type = 1 THEN 2 /* log files */ ELSE 0 END AS [Max Rec Write Stall Avg] , b.physical_name AS [Physical File Name], CASE WHEN b.name = 'tempdb' THEN 'N/A' WHEN b.type = 1 THEN 'N/A' /* log files */ ELSE 'PAGEIOLATCH*' END AS [Read-Related Wait Stat], CASE WHEN b.type = 1 THEN 'WRITELOG' /* log files */ WHEN b.name = 'tempdb' THEN 'xxx' /* tempdb data files */ WHEN b.type = 0 THEN 'ASYNC_IO_COMPLETION' /* data files */ ELSE 'xxx' END AS [Write-Related Wait Stat], GETDATE() AS [Sample Time], b.type_descFROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a INNER JOIN sys.master_files AS b ON a.file_id = b.file_id AND a.database_id = b.database_idWHERE a.num_of_reads > 0AND a.num_of_writes > 0ORDER BY CAST(a.io_stall_read_ms / ( 1.0 * a.num_of_reads ) AS INT) DESC;https://www.brentozar.com/blitz/slow-storage-reads-writes/

SELECT (SELECT SUM(CAST(size AS BIGINT)*8/1024/1024 FROM sys.master_files WHERE type=1) AS logGB,

(SELECT SUM(CAST(size AS BIGINT)*8/1024/1024 FROM sys.master_files WHERE type=0) AS rowGB

logGB rowGB----------- ----------- 11 9512

SSMS Activity Monitor

TODO

Tables & Views

sys.dm_io_pending_io_requests
sys.dm_io_virtual_file_stats
sys.dm_os_wait_stats

Bibliography

https://stackoverflow.com/questions/48425245/what-can-cause-high-buffer-i-o-on-my-clients-server https://www.brentozar.com/archive/2012/03/how-fast-your-san-or-how-slow/https://www.brentozar.com/blitz/slow-storage-reads-writes/https://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/https://mssqlwiki.com/sqlwiki/sql-performance/io-bottlenecks/