SQL server number of data files per file group

Question

SQL server number of data files per file group

Ashwan 536

hi experts

on prem

SQL version : 2019

Virtual VM machines/ alwayson enabled

use it: OLTP system

We have large database over 1.5TB database on 10 datafiles . each 200gb aprox. could any one confirm any limitation or performance impact holding all data files on one file group? with the demand , keep creating more datafiles on single files group would be the best practise ?

thanks

2 answers

Your answer

Answer 1

Olaf Helper 47,436

We have large database over 1.5TB database

Nowadays 1.5 TB isn't that very large and SQL Server can easily handle it if everything on data is in one database file.

If it's located on a well performing SAN, then the count of database groups/file doesn't matter much.

Keep it as it is now.

Answer 2

ZoeHui-MSFT 41,491

Hi @Ashwan,

Please check the official documentation.

Recommendations when working with files and filegroups:

Most databases will work well with a single data file and a single transaction log file.
If you use multiple data files, create a second filegroup for the additional file and make that filegroup the default filegroup. In this way, the primary file will contain only system tables and objects.
To maximize performance, create files or filegroups on different available disks as possible. Put objects that compete heavily for space in different filegroups.
Use filegroups to enable placement of objects on specific physical disks.
Put different tables used in the same join queries in different filegroups. This step will improve performance, because of parallel disk I/O searching for joined data.
Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups. Using different filegroups will improve performance, because of parallel I/O if the files are located on different physical disks.
Don't put the transaction log file(s) on the same physical disk that has the other files and filegroups.
If you need to extend a volume or partition on which database files reside using tools like Diskpart, you should back up all system and user databases and stop SQL Server services first. Also, once disk volumes are extended successfully, you should consider running DBCC CHECKDB command to ensure the physical integrity of all databases residing on the volume.

For more information on transaction log file management recommendations, see Manage the size of the transaction log file.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Ashwan 536 Reputation points

2023-06-05T08:34:11.6633333+00:00

hi ZoeHui-MSF

current days splitting multiple datafiles across multiple work load with diffrent file groups not really matter I think . end of the day all datafiles site on one LUN . so not sure why we need different file groups then
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-06-05T21:34:17.0933333+00:00

I agree, and this is also what Olaf said. Sure, you might gain some performance by creating filegroups, if you do it right, but finding out what is right is certainly not trivial. And, if all goes into the same LUN, yeah, it's not likely to be worth the effort.
Olaf Helper 47,436 Reputation points

2023-06-06T06:29:05.7033333+00:00

Most databases will work well with a single data file

Agree and that's my experience over the last 20 years.
Ashwan 536 Reputation points

2023-06-07T01:27:00.6166667+00:00

I agree with Olaf and Erland. so having one file for terabyte size database over having multiple files or multiple files with different file groups wouldn;t have that benefit . only thing i can see if lost any data file , due to storage issue or human mistake then restore multiple files with small size over large size one file may quicker . you guys still see any trivial on that point?
Ashwan 536 Reputation points

2023-06-07T04:58:25.3333333+00:00

I found this from SQL server documentation under the databases
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-06-07T21:50:05.37+00:00

I agree with Olaf and Erland. so having one file for terabyte size database over having multiple files or multiple files with different file groups wouldn;t have that benefit . only thing i can see if lost any data file , due to storage issue or human mistake then restore multiple files with small size over large size one file may quicker . you guys still see any trivial on that point?

It's correct that with many files/filegroups, you can do a piecemeal restore, so if only one file has gone bad, you can restore that one. But with all files on the same SAN, how could that happen.

If you have many filegroups, you can start with restoring the most important file group(s) and take it online, and restore filegroups for archive tables etc later on.

But the question is: will you ever do that kind of restore? And what if you planned wrong, and actually need a table from that filegroup you have not yet restored?

Share via

SQL server number of data files per file group

2 answers

Your answer