• One of the things you are doing with a large number of physical files is forcing SQL Server to use at least one IO stream per file (assuming all files are needed to answer your query). SQL may decide that your access plan would benefit from multiple IO streams to some or all of the files.

    Therefore if your database is held in 80 files instead of 1, then you are forcing SQL Server to use at least 80 IO streams. This could be good for high-speed parallel access to data, but only if your storage subsystem can handle the load.

    If you plot response time to % busy on your storage system, you are almost certain to find a hyperbolic curve, where response time looks OK up to maybe 95% busy but then deteriorates rapidly.

    If you force too many IO streams into your subsystem, you may find one or more components get too near 100% busy. Your IO performance becomes far worse than if you had fewer IO streams and maybe 80% busy storage subsystem.

    What is 'too many' will depend on your storage subsystem. It may be worth working with your storage expert to model the potential IO rate if no bottlenecks existed compared to the physical characteristics of the data transfer rate on your motherboard, NIC, and external storage subsystem capabilities.

    You can then have a better idea of the maximum throughput possible with a given response time. From there you can look at either getting investment to improve the hardware to cope with the requested IO or spending time reducing the file count to match what the hardware can do.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara