Calculate SQL Disk Write and Read latency

10/29/2018 9:27 PM

11/28/2023 6:45 PM

The Storage I/O feature in DPA frequently collects file statistics from a monitored database instance. If the instance includes a large number of files, collecting I/O data can incur excessive overhead for both the monitored instance and for DPA. To avoid performance issues, DPA automatically disables storage I/O polling when the number of files exceeds a specified limit. The default limit is 250.

Database Performance Analyzer

- N/A

Polling Queries

I/O stats polling is done every 20 seconds (default value, IO_STATS_POLL_INTERVAL) and is stored in memory.
 

SQL Server Poll Query
SQL Server Statistics SQL

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT a.database_id,
       a.file_id,
       b.name as fname,
       db_name(a.database_id) AS dbname,
       a.num_of_reads,
       a.num_of_writes,
       a.io_stall_read_ms as read_latency,
       a.io_stall_write_ms as write_latency,
       a.num_of_bytes_read,
       a.num_of_bytes_written,
       UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location
 FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
 JOIN sys.master_files b ON a.file_id = b.file_id AND a.database_id = b.database_id
ORDER BY a.database_id

• Read Latency: (Change in io_stall_read_ms / Change in num_of_reads)
• Write Latency: write_latency
• Read Throughput: (Change in num of reads) * (avg block size (for reads) * 1024)
• Write Throughput: (Change in num_of_writes) * (avg block size (for writes) * 1024)

Oracle Poll Query
Oracle Statistics SQL

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

-- These are data files
SELECT kcfiofno AS file_id,
       kcfiopyr as num_of_reads,
       kcfiopyw as num_of_writes,
       kcfiopbr as num_of_blocks_read,
       kcfiopbw as num_of_blocks_written,
       kcfioprt as readtime,
       kcfiopwt as writetime
  FROM x$kcfio
 WHERE kcfiofno <= ?
 UNION ALL
-- These are temp files
SELECT kcftiofno + [maxDataFiles] AS file_id,
       kcftiopyr as num_of_reads,
       kcftiopyw as num_of_writes,
       kcftiopbr as num_of_blocks_read,
       kcftiopbw as num_of_blocks_written,
       kcftioprt as readtime,
       kcftiopwt as writetime
  FROM x$kcftio
 WHERE kcftiofno <= ?;

• Read Latency: (Change in READTIM) / (Change in PHYRDS)
• Write Latency: (Change in WRITETIM) / (Change in PHYWRTS)
• Read Throughput: (Change in PHYBLKRD * (blocksize/1024))
• Write Throughput: (Change in PHYBLKWRT * (blocksize/1024))

Notes: readtime and writetime conversions to milliseconds done by DPA:

• Oracle 11.2 and above will multiply by .001 (divide by 1000)
• Oracle 11.2 and lower will multiply by 10