Single Virtual Drive Vs Multiple Virtual Drives for SQL Server files

Question

Hi guys I have question about how two different Hard disk setups can possibly affect performance of my database.

I have two Options.

Option 1:

One Physical Harddisk and then Multiple Virtual hard drives for SQL Server's Database files (.mdf , .ndf),Log files, TempDB, Backups etc

Option 2:

One Physical Harddisk and then One huge Virtual hard drive for SQL Server's Database files (.mdf , .ndf),Log files, TempDB, Backups etc

To my knowledge, If these were all physical hard drives, having multiple drives for all these files (tempDB, Log file, backups, database files), would definitely benefit from parallel processing.

But is there any drawbacks of having one massive Virtual Hard drive vs multiple Virtual Hard drives as in reality, when Sql Server is reading or writing , it will be all done from one Physical Hard disk?

Is one method preferred over other? Any suggestions or pointers in the right direction is much appreciated. Thanks

Answer 1

There is no appreciable difference in performance when the underlying hardware is the same, assuming we're talking about a single physical disk.

However, separate logical drives might help you maintain your sanity, and could result in lower physical fragmentation of the SQL-related volumes

Answer 2

It will be very difficult to guess in advance exactly how big each of your virtual drives should be. If you get it wrong you will run out of space for, say, data files while log and TempDB have plenty of unused space on their logical disks. You will not be able to assign this unused space to data files without creating a complicated mess. So I'd suggest one virtual drive and allow the free space to be used by whichever process needs it the most at runtime.

Answer 3

I am definitely in favor of creating a separate virtual drive for backups. Even if all the arguments for ease of management apply, the backup files (full, differential and transaction log files) are constantly being written and deleted. The other types of files (.mdf, .ndf, .log, etc) stay fairly consistent. So why mix the two types just because you don't want to come up with reasonable estimates? I have found that when splitting them out like this, only my backup virtual drive gets severely fragmented.