Recently I have been having serious SAN issues. When looking at the usual suspects for slow downs (excessive locking, blocking, wait states, Deadlocks, high CPU) all looks good, However, upon examination of the SQL error and application event logs I immediately noticed error messages similar to the following:
SQL Server has encountered 523 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [g:\log\filename.ndf] in database [dbname] (18). The OS file handle is 0x0000000000000E14. The offset of the latest long I/O is: 0x000002cef4c000
That would certainly explain the slow downs and application timeouts. The following code will allow you the set up a custom alert for SQL Server message id 833 (found in the application event log).
EXEC msdb.dbo.sp_add_alert @name=N’833-Alert’, @message_id=833, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=0, @job_id=N’00000000-0000-0000-0000-000000000000′
EXEC msdb.dbo.sp_add_notification @alert_name=N’833-Alert’, @operator_name=N’Your Operator Group’, @notification_method = 1
As with any script you get off the internet please review for appropriateness in your environment. NJOY and I hope this is helpful.