1

I read a lot about performance improvement if instead of a single large .mdf file you partition it by multiple (.ndf) files.

But I also found from documentation that the same improvement can be accomplished if a file created on RAID.

So would that make sense for me to partition .mdf file into multiple files or should I just leave it as is, since it's on RAID?

enter image description here

Improve this question
4
  • Do you have reason to believe you have file contention?
    – Brendan McCaffrey
    Commented Jan 22, 2022 at 0:37
  • No, I don't think it's file contention. Both files created at the same time.
    – Serdia
    Commented Jan 22, 2022 at 0:58
  • 3
    Brendan's point is, you may be trying to optimize something that's not needed. Partitioning the database into multiple NDF files is generally not the first goto for improving performance, especially with disks being faster nowadays. Maybe back on mechanical hard drives, there's some opportunity for performance improvement.
    – J.D.
    Commented Jan 22, 2022 at 1:29
  • Got it. There is no really needs to do it at this point. It's just the size of the database is about 6 Tb (although one table takes around 80% of it). So I thought maybe it's a good practice to keep multiple files for VLDB.
    – Serdia
    Commented Jan 22, 2022 at 1:56

1 Answer 1

Reset to default
4

It is a good practice to have multiple files in each filegroup for a VLDB.

Having more than one file can sometimes improve performance a little bit, but that's not the main reason.

When you reconfigure your storage, or restore or move the database to a different server, you have more options if you have, say 6 1TB files than 1 6TB file. Having 6 files allows you to run the database on systems that have 1,2,3, or 6 separate disks. Or to add a 1,2, or 5 additional disks and spread the database across the disks.

With modern storage and operating systems there are many ways to pool disks together, but compatibility with systems having various disk configurations is of some value.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.