Alerts for Disk avg. read time & write

Tradebridge_DBA's

Hi All,

This might a simple question but here it goes....

We are currently having issue with our Disk avg. read time & write speeds where it randomly shoots up above 300ms ....

we would like to know if there is Alerts for Disk avg. read time & write..?
We know that you are able to view the Disk avg. read time & write on the analysis screen but cant find a custom metric or plain metric setting for this...

Is there any way someone can guide us on how to we can create a alert for this.

Thanks in advance!!!

priyasinha

Hello,

Unfortunately, SQL Monitor doesn't support creating alerts on the metric you see on Analysis page.

You can create custom metric and alerts but these are limited to only data which can be queried using T-SQL.

If you think that it would be good to have this feature in product then please do add it to http://sqlmonitor.uservoice.com/.

Thanks,
Priya

sql-lover

Make a job of this ...

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
WHERE (io_stall / (num_of_reads + num_of_writes)) >20
ORDER BY Latency DESC
--ORDER BY ReadLatency DESC, Drive;
GO

Save on a table and aggregate the read column (or write). Make the job to send you an alert based on that.

Good luck!