1

Running SP_Blitz indicated that I have a slow read/write performance on my drives. Reading further, I eventually landed on this query to view the actual statistics of IO occurring on my drives:

SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads, 
a.num_of_writes, 
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms, 
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb, 
a.database_id,
db_name(a.database_id) AS dbname, 
b.name, a.file_id, 
db_file_type = CASE 
                   WHEN a.file_id = 2 THEN 'Log' 
                   ELSE 'Data' 
                   END, 
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.io_stall DESC

enter image description here

*Row #1 is the database mdf, #2 is transaction log

According to the result, my database should practically be unusable but this isn't the case. We're operating a fairly large OLTP database with hundreds of transactions occurring at any given second, yet there isn't any sign of performance issues (at least none that we heard from our customers).

Should I actually be worried about the above statistics? Is there an actual problem I need to address or is it simply the case that query is incorrect for our case?

If there is a problem, where should I start?

5

You need to divide io_stall_read_ms by num_of_reads, and io_stall_write_ms by num_of_writes. This will give you something more sensible as what you are seeing for read and write stalls and sum totals since your instance last stated up.

However, using this DMV in this manner can average out any spikes and hide patters, therefore you need to take samples from it every so often. Also I would recomend putting looking at your page life expectancy in conjunction with read latencies, go off Jonathan Kehayias's formula from SQL Skills.

Note that you need to look at PFE per NUMA node and if your application is OLAP you are unlikely to get a great PFE, your sequential scan rates being the more important metric here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.