Sql-server – Single Virtual Drive Vs Multiple Virtual Drives for SQL Server files

Conventional wisdom about RAID doesn't apply well to SSDs. They don't really need striping (RAID0). They are prone to failures by-design, but RAID-1 is usually not the right answer for SSD for two reasons: a) is wasteful, halves the capacity of the SSD array (and they are pricey) and 2) SSDs failure characteristics leads towards both drives in the mirror to fail at very close intervals (ie. correlated failures) and thus render the entire array useless. See Differential RAID: Rethinking RAID for SSD Reliability for a lengthier discussion. Some have recommended using Raid-6 for SSDs.

Additionally, the conventional wisdom of SQL Server file layout doesn't apply to SSDs. I would recommend you watch SQL on SSDs: Hot and Crazy Love and go over the benchmark links in this answer.

But recently I came across some database on a server where every database had multiple mdf files.

That is because of incorrect naming convention. Microsoft says that every database has one primary data file but it does not means it can have only one "mdf data file", database can have many data file with .mdf extension but only one will be primary data file. It is better to give mdf extension to primary data file and ndf to secondary data file to have proper demarcation but this is no hard and fast rule you can also give .abc extension to primary data file so what you witnessed is normal. As a fact you can give any extension you like.

Is there any wisdom in having multiple .mdf files for a database?

If you mean:

Is there any wisdom in having multiple primary files for a database?

The answer is no, a database can have only one primary data file.

But if you mean:

Is there any wisdom in having multiple data files (.mdf, .ndf, or differently named) for a database?

It depends, you can and cannot have advantage with multiple data files. If they are spread across different physical drives (I am talking about spindles) you would see some advantage with write intensive application. If they are all on same logical partitions it won't have any advantage because underlying they would be using common resource. Using files and filegroups improves database performance, because it lets a database be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that is made up of three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can access the data in parallel at the same time.

As per this MSDN BOL article

Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.

Other advantage which I know is consider a 1 TB database if you would have single data file for it and you want to restore this database on some other server its highly unlikely you would have 1 TB of free space. Now if the same database is spread on different files each having size 250 G it becomes easier in restoration. This might not be actually your scenario but it does helps a lot its little easy to find a server with four 250 G drives than one 1 TB drive

I would say instead of many data files its better to have different file groups but again there are not many environments. Databases made up of multiple filegroups can be restored in stages by a process known as piecemeal restore. Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple mode. Files or filegroups in a database can be backed up and restored individually. This allows you to restore only the damaged files without having to restore the rest of the database. Files in a filegroup backup can be restored individually or as a group