Are warnings collected and expressed on reaching a request threshold or on schedules? Can anyone explain the rules behind this warning log?
Let me start by saying that this is mostly not a SQL Server issue, unless you have totally flawed system. The message means that SQL Server waited more than 15 seconds for one of the I/O requests to complete. For example when SQL Server wants a data page into memory from disk it would initiate an I/O request, if that request takes longer than 15 seconds such messages are logged into errorlog. Normally, on a good system, an I/O response should complete within milliseconds but if this warning is coming this mostly indicates
- The SQL Server is producing more I/O for a storage subsystem to handle and thus overwhelming it with I/O responses
- The underlying disk subsystem is slow, outdated, not on updated firmware and so cannot cope up with SQL Server I/O requests.
Quoting from Blogs.msdn
The message indicates that SQL Server has been waiting on at least one
I/O for 15 seconds or longer. The exact number of times you have
exceeded this time for the specified file since the last message is
included in the message. The messages will not be written more than
once every five minutes. Keep in mind that read IOs on an average
system should take no more than 10-20ms and writes should take no more
than 3-5ms (the exact acceptable values vary depending on your
business needs and technical configuration). So anything measured in
seconds indicates a serious performance problem. The problem is NOT
within SQL Server, this message indicates SQL has sent off an IO
request and has waited more than 15 seconds for a response. The
problem is somewhere in the disk IO subsystem. For example, the disk
IO subsystem may have more load than it is designed to handle, there
is a "bad" hardware or firmware somewhere along the path, filter
drivers such as anti-virus software are interfering, your file layout
is not optimal, or some IO subsystem setting such as HBA queue depth
is not set optimally.
There two phases of analyzing this message by SQL Server, recording and reporting. More details in This support Article
Recording There are two moments when a record action occurs in SQL Server. The first is when the I/O operation actually finishes. If an
I/O request takes more than 15 seconds to finish, a record operation
occurs. The second moment is when the lazy writer runs. When the lazy
writer runs, the lazy writer checks all the pending data and all the
pending log file I/O requests. If the 15-second threshold has been
exceeded, a record operation occurs.
Reporting occurs in intervals that are 5 minutes or more apart.
Reporting occurs when the next I/O request is made on the file. If a
record action has occurred and 5 minutes or more have passed since the
last report occurred, the informational message that is mentioned in
the "Summary" section is written to the SQL Server error log.
The 15-second threshold is not adjustable. However, you can disable
stalled or stuck I/O detection by using trace flag 830, although we do
not recommend that you do this.
- Make sure SQL Server data and log files are distributed on different drive this will give some I/O relief
- Make sure drive on which SQL Server files reside are not compressed
- Get your storage checked from storage team/vendor.
- If the message says I/O request taking longer for tempdb make sure you have configured tempdb properly. Make sure trace flag 1117,1118 is enabled. This support article should help you.
To find out whether disk is slow or SQL Server is spawning more I/O's which disk could handle you would have to take help of perfmon counters and DMV
Sys.dm_io_virtual_file_stats. For perfmon counters and how to gauge whether disk is issue or it is SQL Server please refer to blog by Karthik PK.
For DMV, Paul Randal owner of SQLSkills.com has Caputing IO latencies for period of time. This will let you capture I/O latency for period of time and analysze. If you have HDD or flash drives and see latency in seconds that is pointing to storage slowness issue.
Also read Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data