Poor Disk Performance, Time Outs, Database and the Errorlog: SQL Server has encountered % occurrence(s) of IO requests taking longer than 15 seconds to complete on file
The SQL Server errorlog provides a wealth of information and is often overlooked, even though it should be the first place we all turn to. The problem though is that most people look at the errorlog only after a problem has occurred instead of proactively looking at it as part of the daily list of tasks.
I've written a TechNote on a common error that affects the disk subsystem and the database and shows up in the errorlog as:
SQL Server has encountered 4507 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [D:\Program Files\Microsoft SQL Server\MSSQL\Data\BIGDB_Data.MDF] in database [BIGDB] (7)
While many people have blogged on WHAT this issue is and WHAT to look at. Microsoft has published KB Article 897284 on the topic—which gives a good DESCRIPTION. I want to explore more of the areas around WHY it happens and suggest some actual suggestions on HOW to fix it.
Please visit the Computation Press website on December 26, 2007 and I'll have the note posted.