0

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.

2 Answers 2

1

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.

1
  • Thank you for your answer. Our production server is using VMAX and ridiculous number of CPUs. The path I pasted here is from staging server and we don't load it much. I would say we don't load it at all.
    – Natalia
    Nov 17, 2011 at 15:12
1

The error could indicate anything from under-spec'ed or faulty hardware (we just had that error and it was a bad cable going to a PowerVault) to a misconfigured database or RAID configuration.

The IO delay is occurring on your transaction log file which I'm assuming is on the same array as your databases. You should see a benefit by configuring two small drives as a separate RAID-1 (mirror) array and moving the transaction log there.

2
  • Thanks for your answer. On production all our databases are on different hard drives so I still have no clue what's going on :)
    – Natalia
    Nov 28, 2011 at 20:07
  • We'll need some more info to help... What kind of hardware do you have? How are each of the RAID arrays configured? RAID-5, RAID-10? Are you only getting the error on QA_Database.ldf or on other files as well? Also, can you check the event logs in Windows (particularly hardware) to see if anything comes up there? Can you get a pattern on when the error occurs (e.g. does it only occur during an ETL process or backup...)? Nov 29, 2011 at 7:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .