{"id":4990,"date":"2019-05-16T14:15:12","date_gmt":"2019-05-16T21:15:12","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4990"},"modified":"2019-05-16T14:20:27","modified_gmt":"2019-05-16T21:20:27","slug":"the-curious-case-of-does-sql-server-use-a-readwrite-thread-per-lun","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-does-sql-server-use-a-readwrite-thread-per-lun\/","title":{"rendered":"The Curious Case of… does SQL Server use a read\/write thread per LUN?"},"content":{"rendered":"

(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0newsletter<\/a>\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)<\/p>\n

I had a question in email this morning from someone that was basically does SQL Server use a read\/write thread per LUN?<\/p>\n

The short answer is no.<\/p>\n

My friend Bob Dorr from CSS actually debunked this publicly way back in\u00a0 2007 in the blog post\u00a0SQL Server Urban Legends Discussed<\/em><\/a> and also explains how some of the wording in earlier versions of Books online led to the myth that SQL Server uses one read\/write thread per LUN or per data file. This myth leads people to believe that adding more data files will increase I\/O performance because more threads will be used to do the writing. While it’s true that more data files can increase performance, depending on your I\/O subsystem and the file\/filegroup layout on that I\/O subsystem, it’s nothing to do with that myth.<\/p>\n

Any thread in SQL Server that’s processing a query can issue an asynchronous read request. When a thread makes a call into the buffer pool to get\u00a0 a pointer to a data file page copy in memory, if the page isn’t already in memory then it has to issue a read and then wait for the read to complete (called a physical I\/O). On the other hand, if the page already is in memory, the thread pops back out to where it came from (usually the Access Methods code) and that’s a logical I\/O, with no accompanying wait. Pages can also be read in larger chunks by things like index seek\/scan readahead and DBCC CHECKDB<\/em>‘s readahead.<\/p>\n

Pages can be written to disk in a variety of ways, including:<\/p>\n