All we need is an easy explanation of the problem, so here it is.
We’re working with a vendor who is insisting we use MAXDOP = 1 (I know…). They are also emphasizing the importance of striping our data files across different disks.
As I understand it, an I/O operation is always paired with a CPU cycle. So if we have tables that were spread over different disks, wouldn’t MAXDOP = 1 make reads/writes occur sequentially rather than in parallel?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
…wouldn’t MAXDOP = 1 make reads/writes occur sequentially rather than in parallel
No. SQL Server almost exclusively uses asynchronous I/O. Any worker may issue an I/O request, which is then handed off to the operating system and I/O subsystem to complete.
The worker is free to do other work (query-related or even issuing more asynchronous I/O) while previously-issued I/O requests are being processed in the background.
In this way, SQL Server can drive high I/O throughput from even a single worker thread, using read-ahead for example. SQL Server manages the total number and depth of I/O requests issued to maximize throughput while maintaining good response times.
A main benefit of striping your data across multiple I/O subsystems is to improve the total I/O bandwidth available to SQL Server. With multiple I/O paths available, SQL Server will try to drive each toward its maximum capacity.
While issuing (and subsequently completing) asynchronous I/O is not a particularly time-consuming activity, it is possible for multiple threads in a parallel query to drive I/O even harder than a single worker. Even so, a system with multiple concurrent queries running on a single thread, each issuing large read-ahead, can easily saturate even very capable I/O systems.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂