Alerts for Disk avg. read time & write

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Alerts for Disk avg. read time & write

Postby Tradebridge_DBA's » Wed Apr 10, 2013 4:12 pm

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!!!
Tradebridge_DBA's
 
Posts: 2
Joined: Wed Feb 20, 2013 8:38 am
Location: South Africa

Postby priyasinha » Thu Apr 11, 2013 5:47 pm

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
priyasinha
 
Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm

Postby sql-lover » Wed Apr 24, 2013 7:39 pm

Make a job of this ...

Code: Select all
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!
sql-lover
 
Posts: 17
Joined: Tue Feb 12, 2013 9:26 pm


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests