Wednesday, May 30, 2012

SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete


This morning, while going through my regular SQL Server Logs reports, for one of our production server, I found a different error.
SQL Server has encountered 52 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TempDB.mdf] in database [TempDB] (2). The OS file handle is 0x00000884. The offset of the latest long I/O is: 0x00000457490000
First thing that I searched about this error was that is this a critical message?
And answer I found was YES. Basically when talk about I/O in SQL Server, we always have measurements of mille seconds in our mind and waits of several seconds is considered too odd. SQL Server I/O wait time can be examined by following query:
SELECT  *
FROM    sys.dm_os_wait_stats
WHERE   wait_type LIKE 'PAGEIOLATCH%'

How to check you hard drive performance?
To check, server IO subsystems I trust on Performance Monitor IO Counter PhysicalDisk Object: Avg. Disk Queue Length. Monitor this counter for at least 10 minutes. If the Avg. Disk Queue Length exceeds 2 for next ten minutes for each individual disk drive in an array, then it is sure that you have IO bottleneck.


Who is the culprit, SQL Server or Operating System?
Problem is only your SAN or Local disk IO subsystem. In my case, I found that few other applications were also installed by client on same drive and which were pushing SQL Server to wait for too long to complete its IO requests.

No comments:

Post a Comment

All suggestions are welcome