Need T-SQL to add status

Question

Post reply

Need T-SQL to add status

bjohnston.okc

Valued Member

Points: 54
More actions
January 28, 2014 at 10:33 am

#288822

New to t-sql and needed to make an addition to this IO Latency check. Need to know how to add a status column that when the resultset equals <this> display <this>
Need to say when result is 0-20 ms status is Fast, when result is 20-50 ms status is Slow, When result is > 50 ms status is Really Slow.
Any help would be greatly appreciated!!!
SELECT LEFT(physical_name, 1) AS drive,
CAST(SUM(io_stall_read_ms) /
(1.0 + SUM(num_of_reads)) AS NUMERIC(10,1))
AS 'avg_read_disk_latency_ms',
CAST(SUM(io_stall_write_ms) /
(1.0 + SUM(num_of_writes) ) AS NUMERIC(10,1))
AS 'avg_write_disk_latency_ms',
CAST((SUM(io_stall)) /
(1.0 + SUM(num_of_reads + num_of_writes)) AS NUMERIC(10,1))
AS 'avg_disk_latency_ms'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
GROUP BY LEFT(physical_name, 1)
ORDER BY avg_disk_latency_ms DESC;

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323755 More actions · Answer 1

wrapping your current query and using a CTE or subquery works;

here's a simple case statemetn example:

SELECT

CASE WHEN avg_read_disk_latency_ms < 20 THEN 'fast'

WHEN avg_read_disk_latency_ms < 50 THEN 'slow'

ELSE 'Really Slow'

ENd As ReadStatus,

CASE WHEN avg_write_disk_latency_ms < 20 THEN 'fast'

WHEN avg_write_disk_latency_ms < 50 THEN 'slow'

ELSE 'Really Slow'

ENd As ReadStatus,

CASE WHEN avg_disk_latency_ms < 20 THEN 'fast'

WHEN avg_disk_latency_ms < 50 THEN 'slow'

ELSE 'Really Slow'

ENd As ReadStatus,

myAlias.*

FROM (

SELECT

LEFT(physical_name, 1) AS drive,

CAST(SUM(io_stall_read_ms) / ( 1.0 + SUM(num_of_reads) ) AS NUMERIC(10, 1)) AS 'avg_read_disk_latency_ms',

CAST(SUM(io_stall_write_ms) / ( 1.0 + SUM(num_of_writes) ) AS NUMERIC(10, 1)) AS 'avg_write_disk_latency_ms',

CAST(( SUM(io_stall) ) / ( 1.0 + SUM(num_of_reads + num_of_writes) ) AS NUMERIC(10, 1)) AS 'avg_disk_latency_ms'

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs

JOIN sys.master_files AS mf

ON mf.database_id = divfs.database_id

AND mf.file_id = divfs.file_id

GROUP BY

LEFT(physical_name, 1)

)myAlias

ORDER BY avg_disk_latency_ms DESC;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bjohnston.okc Valued Member Points: 54 More actions · Answer 2

Thanks Lowell! That's exactly what I was looking for.