1

SQL Server log shows batches of warnings, of the type:

SQL Server has encountered 18757 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\..

SQL Server has encountered 951 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [M:\..

Our server is very busy in the hours before these warnings. Are they related to very recent requests or all requests since the last time a similar warning occurred? I am trying to tie in the warnings to identify the jobs that might have caused them. We have a long running job that finishes 10 minutes before these warnings occur. Are warnings collected and expressed on reaching a request threshold or on schedules? Can anyone explain the rules behind this warning log?

1

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

  1. The SQL Server is producing more I/O for a storage subsystem to handle and thus overwhelming it with I/O responses
  2. 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

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.

Troubleshooting

  1. Make sure SQL Server data and log files are distributed on different drive this will give some I/O relief
  2. Make sure drive on which SQL Server files reside are not compressed
  3. Get your storage checked from storage team/vendor.
  4. 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

  • Thank you. We are aware of inefficiencies in our procedures (unnecessary use of tempdb) that can be addressed and how to address them, but want to target the particular procedures causing our disk contention. This is a SQL Server problem and can be remedied through SQL procedure changes. I want to know the relationship between our procedures with inefficient disk-usage and the timings of the warnings. Did you answer that question here: The messages will not be written more than once every five minutes. -- Does this mean that contention was within 5 minutes of the warning? – cloudsafe Jul 5 '18 at 10:10
  • @cloudsafe I have updated my answer please check. As for your question whenever an I/O takes longer than 15 sec the action is recorded internally and every 5 mins this action is dumped in errorlog file irrespective of when query ran and finished. I hope this is what you are asking – Shanky Jul 5 '18 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.