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?
Best Answer
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.