November 28, 2013 at 3:56 am
I have been checking my disk i/o and ran sp_blitz script which identified 'slow storage writes on drive S. The link from this output led me to run the following query to check things on a more granular level
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,
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
I have edited the ouput to show the key findings. Can somebody please advise if it looks like I have a problem here ? I have had to attach the output into an excel spread sheet as when i try and post the results using the tags opposite it just makes them unreadable. Please if anybody can advise on how I post the result set from my query I will gladly resubmit this
November 28, 2013 at 5:55 am
Hi,
if you are analyzing IO sub system latency then go through below blog post.
http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply