patternsqlModerate
Did Microsoft change query optimizer regarding number of files and parallelism
Viewed 0 times
numberdidqueryregardingmicrosoftfilesoptimizerandparallelismchange
Problem
Did Microsoft change query optimizer regarding number of files and parallelism? Does the optimizer no longer factor in the number of files to determine the degree of parallelism for a query? If so, does anyone know when the change was made? If not, can anyone provide a link to Microsoft documentation that discusses the topic (current documentation for either SQL Server 2014 or 2016)?
Solution
That was an urban legend that Microsoft's Bob Dorr debunked back in 2007:
This information has been taken out of context and propagated
incorrectly as SQL Server has a thread per database file so adding
more files to a database can increase I/O performance. Adding more
files and properly aligning them with storage can increase the I/O
performance for a variety of reasons but achieving new I/O threads per
file is NOT one of them.
Read the post for much more detail.
This information has been taken out of context and propagated
incorrectly as SQL Server has a thread per database file so adding
more files to a database can increase I/O performance. Adding more
files and properly aligning them with storage can increase the I/O
performance for a variety of reasons but achieving new I/O threads per
file is NOT one of them.
Read the post for much more detail.
Context
StackExchange Database Administrators Q#200234, answer score: 14
Revisions (0)
No revisions yet.