Does spreading table data across multiple files (in the filegroup) improve performance?

Question

From the product documentation on filegroups:

For example: Data1.ndf, Data2.ndf, and Data3.ndf, can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1. Queries for data from the table will be spread across the three disks; it will improve performance.

I understand that if there is a very large table, and if I place it in a file group that has a single file and if this is on a separate disk, then it will improve performance because all queries to this large table will go to the separate disk.

I also know about table partitioning using multiple filegroups; each filegroup with one or more files. However this question is specifically about file group in a scenario of an unpartitioned large table.

In the above example, the author says that the performance is improved even if the file group contains multiple files. I am confused about this because if a table is spread across multiple files, then will this not cause slow performance (because the rows will be spread across the files and so to go from one row to the next row which maybe on another file, wont it take a hit on performance)?

Answer 1

As noted by the other users this doesn't improve performance. It does provide two important capabilities.

1. You can add space and IO capacity to a database without moving it by adding a new disk and adding new files to the filegroup and placing them on the new disk, or by moving some of the existing files to the new disk.

2. You can restore the database on to a server that doesn't have a volume large enough for the entire database by spreading out the files across multiple disks.

And

because the rows will be spread across the files and so to go from one row to the next row which maybe on another file, wont it take a hit on performance?

No. SQL Server always reads at least an 8KB page; it never reads a single row from disk. Whether the "next" page is in the same file or not matters little. And SQL Server will allocate space to an object using 8-page Extents, so when reading sequentially, you'll typically read at least 64KB from each file before reading from the next.

To support old-fashoned data warehouse configurations built on simple arrays of spinning disks there is a startup parmeter -E that "Increases the number of extents that are allocated for each file in a filegroup.", but on a modern storage solution, it's rarely used even in DW configurations.

Answer 2

The idea behind multiple files stems from back when there was a bigger hardware performance bottleneck with the disk. Splitting your database across multiple files over multiple disks was a way to improve performance by not being capped at the I/O speeds of a single disk, especially for parallel processes or parallelized queries. Data could theoretically be loaded off disks in parallel. Even a query that ran serially and needed to access multiple files across multiple disks saw no measurable performance hit (because to locate rows across two disks takes almost no additional time).

Now with disks like NVMes and cloud storage options, this methodology has become less relevant over time.

Answer 3

I would say that it is impossible to give a general answer, SQL Server runs on so many different versions, editions, workloads, operating systems, not to mention storage subsystems that there is no single answer to this question. If you want to know for your particular set of circumstances then you will have to create a like for like test and that will take quite a while.

Maybe if I say that this feature has its origins when there was MUCH less memory in Servers and IO was therefore the key bottleneck, to get more IO you added more disks and to use the disks you had to put files on them.