Alerts for Disk avg. read time & write

• 
  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 April 11, 2013 12:47

  - 0 +
• 
  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!

  sql-lover April 24, 2013 14:39

  - 0 +

Add comment

Please sign in to leave a comment.