Is there a benefit to having multiple
SSDs? I assume that with multiple cores there is, since there is less contention?
You should never have more than one
.mdf for a database.
SQL Server doesn't care about the file extension. Best practice, though, is to have a single
.mdf for the one and only primary file (boot file for the database) and
.ndfs for the other files.
I understand that the question is really about having several data files (one
.mdf and x number of
.ndf), but I wanted to make the above clear first.
As for contention, you might be thinking of the tempdb, and how we have several data files for the tempdb. The contention we talk about here is when SQL server needs to find free space, and needs to look at page number 1,2 or 3 at the beginning of the file to find out where there is free space.
It need to protect from other doing the same thing at the same time so ot places a "latch" on the page, like a lock. Having more files allow more than one thread find free space simultaneously.
So the question is whether your particular load is limited by one data file when it need to find free space? How common is it that you have several threads that at the same time need to find free pages/extents? Not nearly as common as it is for tempdb, I would bet.
You can of course monitor this by grabbing snapshots of what we're waiting for to see if you are waiting for latches on these pages (1, 2, 3) for your database. Or use XEvents to find out.
There's no thread limitation per file in SQL Server. I.e., SQL Server can push the disk subsystem as hard with one file as with several files. But of course, your disk subsystem capacity might increase with several files (more I/O channels etc).
Then there is the topic about having more than one file group, but I'm not addressing that here since that wasn't in the question.
Do not forget that data files (.mdf, .ndf or whatever extension you will give) are written in an asynchronous session of writing data from dirty pages in RAM via the CHECKPOINT command, which is triggered in different manner (time schedule, recovery time memory pressure...). So the benefit of having multiple file while IO are written is very few until you will have a very hisgh transacionnal activity.
Reversely, reading data from disk when the data is not in cache, will have some benefits from a good orgenisation of disks, then files.
First, avoid RAID 5, 6, DP and all combinaison of these RAID levels at the physical design of disks storage.
Second, having multiple file will help you if you have multiples cores. And because all cores cannot be used simultanously, having a number of file less the core count will do a good job...