I/O subsystem latencies - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

I/O subsystem latencies

Author

Topic

titeuf
Starting Member

4 Posts

Posted - 2012-03-09 : 02:56:50

Hi,
I try this query to check the latencies:
SELECT
--virtual file latency
ReadLatency = CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,
WriteLatency = CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,
Latency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,
--avg bytes per IOP
AvgBPerRead = CASE WHEN num_of_reads = 0
THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,
AvgBPerWrite = CASE WHEN io_stall_write_ms = 0
THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,
AvgBPerTransfer = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE ((num_of_bytes_read + num_of_bytes_written) /
(num_of_reads + num_of_writes)) END,
LEFT (mf.physical_name, 2) AS Drive,
DB_NAME (vfs.database_id) AS DB,
--vfs.*,
mf.physical_name
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
--WHERE vfs.file_id = 2 -- log files
-- ORDER BY Latency DESC
-- ORDER BY ReadLatency DESC
ORDER BY WriteLatency DESC;
GO

Found here:http://sqlskills.com/BLOGS/PAUL/category/IO-Subsystems.aspx#p1

But I really don't know whether the values are correct or not....

ReadLatency WriteLatency Latency AvgBPerRead AvgBPerWrite AvgBPerTransfer Drive DB
20 2782 1485 63665 64895 64317 P: tempdb
20 2781 1485 63655 64887 64309 P: tempdb
20 2781 1484 63637 64909 64312 P: tempdb
20 2780 1485 63571 64839 64244 P: tempdb
20 2779 1484 63561 64785 64210 P: tempdb
20 2779 1484 63588 64818 64241 P: tempdb
20 2779 1484 63629 64883 64295 P: tempdb
20 2778 1484 63712 64850 64316 P: tempdb
20 2778 1484 63573 64808 64228 P: tempdb
20 2778 1484 63592 64810 64239 P: tempdb
20 2777 1483 63749 64803 64308 P: tempdb
20 2776 1483 63566 64740 64189 P: tempdb
115 85 110 48812 50416 49094 E: MyDatabase
129 84 121 49067 50583 49334 E: MyDatabase
112 82 106 48838 50561 49140 E: MyDatabase
111 82 106 49037 50414 49281 E: MyDatabase
131 80 122 48893 50511 49177 E: MyDatabase
135 79 125 49025 50415 49270 E: MyDatabase
119 79 112 49072 50391 49305 E: MyDatabase
127 79 119 49061 50587 49330 E: MyDatabase
105 78 100 48896 50488 49176 E: MyDatabase
111 77 105 48921 50324 49169 E: MyDatabase
112 77 106 48801 50509 49101 E: MyDatabase
131 77 121 49062 50392 49297 E: MyDatabase
76 25 54 29217 29139 29184 E: MyDatabase

Could you help me?
Thank you

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-03-10 : 09:12:36

This script is useful for making decisions about moving files onto other drives or different IO paths. If you detect an issue - create more test scenarios to confirm the initial observations. For example , you might want to use Perfrmon Disk writes /sec or Disk Reads/sec.
It is important to have benchmark figures - which allows you to compare current performance.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources