Sql-server – SQL Server 2008 R2 SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

sql serversql-server-2008

When I alter or create a stored procedure directly on production or QA database, after a few seconds I start experience timeouts and application becomes unavailable.

Log files shows this error:

SQL Server has encountered 3 occurrence(s) of I/O requests taking
longer than 15 seconds to complete on file [C:\Program Files\Microsoft
SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QA_Database.ldf] in
database [QA_Database] (9). The OS file handle is 0x0000000000000568.
The offset of the latest long I/O is: 0x0000002821a200

We have SQL Server 2008 R2 installed, including latest Service Pack. Production and staging environments are completely separated.

I tried to reproduce it on QA, but to no avail. I have no clue what it could be.

Best Answer

Very simple. It is what it says:

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

SQL Server makes a IO request, it takes more than 10 seconds to complete. Normally it should take - for a high performance database server - 2-3 ms, not 15.000 or more.

The file path:

[C:\Program Files\Microsoft SQL

Makes me suggest you dont have a production server but someone who had no clue how to install a sql server orderd the hardware and set it up. As a result your database files live on the (slow?) system drive instead of having an IO optimized setup, and your disc simply is totally overlaoded. Happens when one runs high performance databases on a system disc instead of an optimized disc layout. Database servers are tricky to configure hardware wise.

Only solutions are:

  • Check for missing indices. Table scans - especially wehen memroy is not enough - are very IO itensive and can overload every IO subsystem.
  • If query optimization does not yield results, then get a proper deatabase server with a number of faster discs. At the end, your productzion database may jsut need more IOPS than the slow system drive provides.

Just as exmaple, I have a smaller database here (800gb) and I use 8 Velociraptors as a RAID 10 for the databae file and 2x120gb SSD for the logs - which nicely separates out the log IO from the database IO. And I am not happy with the database file disc IO and will soon upgrade with SSD caches.

NOTHING (except the very low usage system databases) exists on C.

The fact that you dont mention your disc layout on a IO related question makes me suggest you dont know how databases work IO wise and someone just ordered a cheap dell server thinking that "it has a xeon, so it is fast" while databases often dont care about the CPU and are totally IO bound.

Related Topic