On our SQL Server environment recently we are getting the following entries:
“SQL Server has encountered 5877 occurrence(s) of IO requests taking longer than 15 seconds to complete on file “
How serious is this? What would the steps be to troubleshoot this?
Solution:
From the outlook of the error it is clear that the Server sub-system is unable to cope up the pressure from the processes that are requesting more resources and this is an informational message. Since the release of SQL Server 2000 Service Pack 4 has included the logic to detect the stalled and stuck conditions for a database that is waiting on I/O reads and writes including the log file I/O reads and writes. Basically when an I/O operation has been pending for 15 seconds or longer the SQL Server engine will perform a detection of that pending operation and writing the informational message to SQL Server error log.
Though this is an informational message that indicates that the current load is experiencing lot of contention due to the following reasons:
| • | The workload is exceeding the I/O path capabilities. |
|
• |
The workload is exceeding the current system capabilities. |
|
• |
The I/O path has malfunctioning software; perhaps a firmware or a driver issue. |
|
• |
The I/O path has malfunctioning hardware components. |
Stuck I/O is defined as an I/O request that does not complete. Frequently, stuck I/O is a stuck IRP. To resolve a stuck I/O condition, you typically must restart the computer or perform a similar action. The reason for this behaviour could be a faulty hardware or a bug in I/O path component on that hardware.
Stalled I/O is defined as an I/O request that does complete. However, the I/O operation takes excessive time to complete. The reason for this behaviour could be hardware configuration, firmware settings or a driver issue that requires assistance from the hardware vendor to resolve.
From the Service Pack 4 diagnostics feature the reporting and the recording of I/O requests are designed on a per file basis. The detection and the reporting of stalled and stuck I/O requests are two separate actions. Overall system performance can play a key role in I/O processing. You should take the general health of the system into consideration when you are investigating reports of stalled or stuck I/O operations. Excessive loads can cause the overall system to be slow. For further information on I/O patterns that are handled by a SQL Server engine refer to the link http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
]]>