Competition: What’s your favorite Redgate tool? Enter now.

Alerts for Disk avg. read time & write

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 :cry: ....

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!!!

Comments

  • 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
    Priya Sinha
    Project Manager
    Red Gate Software
  • 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!
Sign In or Register to comment.