Performance benefits of multiple .ndf files Is there a benefit to having multiple mdfs with 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 &quot;latch&quot; 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.