Red Gate forums :: View topic - Alerts for Disk avg. read time & write
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Alerts for Disk avg. read time & write

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Tradebridge_DBA's



Joined: 20 Feb 2013
Posts: 2
Location: South Africa

PostPosted: Wed Apr 10, 2013 4:12 pm    Post subject: Alerts for Disk avg. read time & write Reply with quote

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 Crying or Very sad ....

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!!!
Back to top
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 531

PostPosted: Thu Apr 11, 2013 5:47 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
sql-lover



Joined: 12 Feb 2013
Posts: 17

PostPosted: Wed Apr 24, 2013 7:39 pm    Post subject: Reply with quote

Make a job of this ...

Code:

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!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group