Sql-server – Does spreading table data across multiple files (in the filegroup) improve performance

filegroupssql server

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)?

Best Answer

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.

Related Question