One SATA Drive vs. Two USB 2.0 Drives with SQL Server

[ScAndal]

As some of you know it is common practice to spread your SQL components across multiple drives. IE. Data files on seperate drive and Log Files on seperate drive.

I had a debate with a co-worker today on performance and I'm wondering what your thoughts are on it.

He explained that using 2 USB External Drives (Log, Data) would be faster than putting the components on 1 SATA Internal drive.

This just doesn't seem like it would be right. I've had experience using USB 2.0 drives and they are slow.

Thoughts?

ScAndal

 

[spyordie007]

It would depend a lot on your data and transactions.

As you probably know there are the data files and the log files, as changes are made they are submitted directly to the log files and are only committed periodically to the data files. The net effect (from a performance perspective) is that you have a lot of reads from but generally infrequent writes to the data file(s) and frequent reads and writes to the log files. For this reason it's not uncommon to find the data files on a RAID-5 array (very fast reads, moderatly fast writes) and the log files stored on a RAID 0+1 array (very fast reads and writes).

So do I think you could get a gain? Maybe if your mix of reads and writes were such that the I/O load was distributed evenly between the data files and logs; but I dont think it would make a huge (practical) differance.

I'm assuming this is hypothetical anyways, because nobody in their right mind would put production SQL data on an external USB drive

 

[ScAndal]

Yes most definately a hypothetical.