How can i alert on I/O Performance of a target in DBA dash to detect poor I/O performance/contention?

[sonalakhal]

HI David,

I hope you are doing well. Is there a way in DBA Dash where we can alert on I/O performance of a certain target base on the MB/Sec value in order to capture poor i/o performance or i/o contention.

Also which table in the database holds the value.

Thank you in advance and awaiting you response.

Regards,
Sonal

[DavidWiseman]

The data is stored in dbo.DBIOStats. For example, you could get the avg MB/sec over the last 5 minutes for an instance using.

SELECT (((SUM([num_of_bytes_read]+[num_of_bytes_written]))/power((1024.0),(2)))/(SUM([sample_ms_diff])/(1000.0))) AS MBSec
FROM dbo.DBIOStats IOS
JOIN dbo.Instances I ON IOS.InstanceID = I.InstanceID
WHERE IOS.DatabaseID=-1
and IOS.Drive='*'
and IOS.FileID = -1
AND SnapshotDate>=DATEADD(mi,-5,GETUTCDATE())
AND I.ConnectionID = 'Your Instance'

If you want an alert you could convert this into an EXISTS with a RAISERROR. If you add this to an agent job, you can then alert on the job failure. e.g.

IF EXISTS(
	SELECT 1
	FROM dbo.DBIOStats IOS
	JOIN dbo.Instances I ON IOS.InstanceID = I.InstanceID
	WHERE IOS.DatabaseID=-1
	and IOS.Drive='*'
	and IOS.FileID = -1
	AND SnapshotDate>=DATEADD(mi,-5,GETUTCDATE())
	AND I.ConnectionID = 'Your Instance'
	HAVING (((SUM([num_of_bytes_read]+[num_of_bytes_written]))/power((1024.0),(2)))/(SUM([sample_ms_diff])/(1000.0))) > ?? /* Threshold here */
)
BEGIN
	RAISERROR('High IO throughput',11,1)
END

Having high throughput doesn't mean poor IO performance - it just means you are reading a lot of data (which could be a problem). Latency is usually a better indicator that the storage is struggling to keep up. If you have low throughput but high IO waits, that might be a problem.

An alternative approach might be to create an alert based on waits. You could alert on PAGEIO% wait type to target IO waits.

Hope this helps.