Seeing 833 event ids related to IO

Question

Hi SQLTeam,

We are seeing a lot of 833 events in windows Application log . This is one of the sub-prod SQL Server environments.
When does an 833 event is logged?
How can we mitigate such issues? Is there a dirty test we can do to simulate the I/Os are taking longer on those drives before engaging storage vendor?
What type of questions we should be asking to the storage vendor?

Windows Application log

SQL Server has encountered 33 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\crmdev\Actifio_1\MSSQL\Data\crm_dat.ndf] in database id 13. The OS file handle is 0x0000000000001B00. The offset of the latest long I/O is: 0x0001dfe9840000. The duration of the long I/O is: 17555 ms.
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\crmdev\Actifio_1\MSSQL\Data\crm_dat.ndf] in database id 13. The OS file handle is 0x0000000000001B00. The offset of the latest long I/O is: 0x00018d617f0000. The duration of the long I/O is: 42453 ms.
SQL Server has encountered 8 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\crmdb\Actifio_1\MSSQL\Data\crm_ind.ndf] in database id 30. The OS file handle is 0x0000000000000358. The offset of the latest long I/O is: 0x0000592bb4c000. The duration of the long I/O is: 24355 ms.

-Bob

66770-io-warnings.txt

Answer 1

Its an Azure VM but few databases are hosted on Actifio 3rd party storage.

So this is the fun with answer questions in forums. Far too often posters give too little information about important things like the environment. For your question, there is quite a difference if it is in your own computer where you have all components in one single cabinet, of it is something in a corporate data centre with a SAN and all that, or if it is an IaaS.

I placed my bets on the middle alternative, since in the corporate case, where is quite much of finger-pointing.

But you have Infrastructure as a Service. That makes it simpler. You open a case with Azure support and tell them that you have unacceptable performance. Forget all I said about networking etc. All that falls within what you are buying from Microsoft, and this is something they need to fix.

Well, I guess that I need to reserve myself for Actifio which I'm completely ignorant of, and I'm a little puzzled how you have 3rd storage in the cloud.

Answer 2

I/O requests taking longer than 15 seconds to complete on file … The duration of the long I/O is: 42453 MS (=42 seconds)

That warning means, that the I/O storage is to slow to handle the I/O requests of SQL Server and 42 seconds is really very slow. Th vendor should review log and test the storage speed.

Answer 3

I suggest you read this:

https://mssqlwiki.com/2012/08/27/io-requests-taking-longer-than-15-seconds-to-complete-on-file/

Answer 4

When does an 833 event is logged?

When an I/O request takes more than 15 seconds to complete, seen from the perspective of SQL Server. 15 seconds is an eternity in this context. With an old-fashioned spinning disk, 15 ms is what you can expect and crave.

So something is seriously wrong here. Necessarily not the disk itself, as there are often many components between SQL Server and the disk. In many organisations there are SANs, and they may be overtaxed. But it may also be the network leading to the SAN etc. Since we don't know the topology for your shop, it is difficult to make suggestions.

But something needs fixing. That's for sure.

Answer 5

Hi @Bob sql ,

> When does an 833 event is logged?

SQL Server database is designed to perform intensive disk I/O operations. An I/O operation may be either a read or a write operation and the standard disk speed to perform these operations is around 10 milliseconds or less. If any of the read or write operation takes more time to perform their activity and generally disk speed reaches or exceeds 15 seconds of time, then database engine reports event id 833 along with above log information in error log file.

> How can we mitigate such issues?

Long I/Os often indicate a SQL Server process that is too intense for the disk subsystem. Disk Subsystems perform poorly/slowly because of either stuck or stalled IO operations.

This problem can also be caused due to system performance issues, hardware errors, firmware errors, device driver problems, or filter driver intervention in the IO process.

Disk I/O problems are one of the most difficult problems to diagnose and to find the root cause. There might be multiple reasons due to which your disk subsystems perform slow. First step towards fixing such issue is to examining the event logs for any hardware related error messages and then proceed towards fix accordingly.

Please check if below blogs could help you.

MSSQLSERVER_833
Fix Event ID 833: SQL Server has encountered 32 occurrence(s) of I/O requests taking longer than 15 seconds to complete.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.