As Wédney said, the improvement would be minimal. In fact, I don’t even know if there will be a real gain, after all I don’t believe that SQL Server can read the two tables in parallel or asynchronously for the same query.
Considering the Implementation Plan
Before starting to read the disk, database systems such as SQL Server first calculate the execution plan. The first step is to check if there is any index with which it can determine in advance which records should be read from the physical table.
If Join is performed only by primary keys on foo
and bar
, then surely there will be indexes. Having enough memory helps SQL Server to keep indexes in memory and thus avoid disk index readings. Therefore, until this step, no reading would be necessary.
With the execution plan calculated, knowing exactly which records should be read from the disk, SQL Server will then start reading the disk records. If they are on separate disks, it may not make a difference, after all it probably needs to wait for a table or record to read to then access another table.
Moreover, even if the two tables are on different disks, nothing guarantees that the movements of the HD needle will be more efficient. The data of the same table can be fragmented in sectors scattered by the tracks of HD.
Completion
Therefore, it may be much more efficient to simply compress your database in order to eliminate unused portions of the file, and then defragment the hard drive,
Answering your question is something that depends on very detailed knowledge about how SQL Server works. If it were an opensource database, most likely someone would have already done a deeper analysis. I can state, however, that there is gain by separating the tables into different disks. Some even separate the indexes from the table itself. The process of separating the tables on different disks is a very quiet thing to do on SQL Server. We do this on a system with database >350 GB. The audit table is in a separate raid and only it is >200 GB.
– cantoni