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?
One observation from us is, the allocation unit size / blocksize is 4K.
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.
Attaching the IO warnings collected from Windows Application log.
-Bob
February 11, 2021 at 10:17 am
Are the sql server files excluded from antivirus?
Does it relate to backup times (more stress on io) / higher cpu usage ( certain batch with lot of iops)?
February 11, 2021 at 10:43 am
No backups are written to these drives.
What is the right way of what is causing these I/O warnings and what needs to be captured or monitored?
Do these correspond to a process that is running at the same time? Reindexing? Updating stats? Something else? If it does not correspond to a process, and is part of a "normal" operation occurring in the database, you can probably trace this to a set of less than optimal code being executed. We had this occurring every day, all day, on the data file on one of our heavily used databases. The disks were configured properly, and that was proved by the fact that none of the other databases were experiencing this issue. Digging in, I traced this to a single proc that had logic flaw in it. It read approximately 35 GB with each execution, and if an update occurred, it updated almost every row in that table. We fixed it, and the issue went away.
Is this a physical machine, or a virtual machine?
Can you provide specs for the disk subsystem?
The 4k block size may be a partial cause, but usually that is only an issue when there is a disk intensive process that is running.
There are a number of tools you can use to simulate load. HammerDB, Diskspd, SQLQueryStress, and SQLIOSim are all free tools.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply