4

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

enter image description here

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

4

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

1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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