May 14, 2014 at 5:47 am
My production instance's SQL error log is flooded with the following message for different databases:
SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQL10_50.prod\MSSQL\DATA\prodappln.ldf] in database [prodappln] (4). The OS file handle is 0x0000000000000BD8. The offset of the latest long I/O is: 0x00000002508400
Also, there are a lot of blocking that are happening and the application has become extremely slower. But I notice that blocking are getting cleared by itself.
May 14, 2014 at 6:07 am
It's absolutely an I/O issue. I'd say your disks are being overwhelmed because the issue isn't that a given process is waiting for disk access while another process uses, but that the processes using the disk are experiencing very slow behavior. It's a hardware issue (or a driver issue, something related to your hardware).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2014 at 6:09 am
yes it is, there could be an issue somewhere in the i/o stack, get your storage people to check it out.
---------------------------------------------------------------------
May 14, 2014 at 6:10 am
Grant Fritchey (5/14/2014)
It's absolutely an I/O issue. I'd say your disks are being overwhelmed because the issue isn't that a given process is waiting for disk access while another process uses, but that the processes using the disk are experiencing very slow behavior. It's a hardware issue (or a driver issue, something related to your hardware).
Exactly what I thought.
I ran the SELECT * FROM sys.dm_io_pending_io_requests DMV and the io_pending column returned the value 1
which implies that I/O is not completed at Operating system level which inturn means I/O is not completed by the disk subsystems.
But the biggest challenge for me is to convince my windows/storage folks about this issue. Any help around that area?
May 14, 2014 at 6:19 am
I feel your pain. Show them the errors and how it is tying in with slow application response and blocking. They absolutely have to check out their hardware and firmware.
---------------------------------------------------------------------
May 14, 2014 at 6:20 am
I'd say show them that error message and let them look it up through Bingle. It's absolutely a hardware related problem and it's very well documented as such out on the interwebs. There's nothing to argue against on this one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2014 at 7:40 am
Grant Fritchey (5/14/2014)
I'd say show them that error message and let them look it up through Bingle. It's absolutely a hardware related problem and it's very well documented as such out on the interwebs. There's nothing to argue against on this one.
Can I have an article which can be used to push the ball to the storage folks?
May 14, 2014 at 8:00 am
Again, if you do a search on the internet, there are tons of articles on this. Not just one. Here's a blog entry from MS on troubleshooting the issue. This is the standard interpretation of the error message from Microsoft. There are more[/url], just look them up on the internet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply