How to disable IO timeouts

Question

On my dev box I sometimes need to run very IO intensive queries such as index builds and CHECKDB. This can put so much load on the disk that it hardly can process anything else. This causes enormous lagging in other programs. For that reason I sometimes need to suspend sqlservr.exe using Process Explorer.

If I do that for longer periods of time I sometimes get IO timeouts such as

Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT', id 0000000160673C40, type 4, Task 0x000000013BBD5C28 : 22, waittime 300 seconds, flags 0x1a, owning task 0x000000010F91F088. Continuing to wait.

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [....ndf] in database id 5. The OS file handle is 0x0000000000000FDC. The offset of the latest long I/O is: 0x00000e07c60000

A time-out occurred while waiting for buffer latch -- type 2, bp 0000000200E9FD80, page 1:30481, stat 0x2040d, database id: 20, allocation unit Id: 72057594068533248, task 0x00000001CCE93088 : 13, waittime 300 seconds, flags 0x1a, owning task 0x00000001CC0FBC28. Not continuing to wait.

This sometimes causes the query to abort. How can I disable IO timeouts on my dev box?

Also, in case the query does not abort I sometimes get dump files. How can I disable those?

Answer 1

Since you're using SQL Server 2014 I would use resource governor to limit the amount of IO. Obviously this will make the processes take MUCH longer and you still may have timeouts.

There is no way to disable the IO messages and potential issues except to get a better IO subsystem or do less IO intensive items.

Answer 2

As Sean said you cannot disable IO though you may be able to use Resource Governor.

Otherwise I think the safest option for you, if you have another licensed copy of SQL Server, is to restore backups and do the CHECKDB on the alternate server.

In SQL 2016 you can use a MAXDOP hint with CHECKDB which will do something similar without having to touch RG.

You may also want to check that you don't have automatic file growths during your CHECKDB (you can check past history in your default trace). I have seen conditions where files are set to proportional growth, and data or log files grow during CHECKDB, and the chunks are so large you see IO timeouts.

You can also try changing the CHECKDB to only use WITH PHYSICAL_ONLY. This will skip important internal integrity checks but sometimes, if you have no choice, it's better than having to disable it entirely. The reason this is relevant to IO errors is that on some low-spec systems redlining your CPUs will result in IO timeouts.