A lot of people also forget about the "striping" that SANs do for things like RAID 5 or Raid 10. There are usually 5 sets of spindles and 5 sets of R/W heads in play. Except for TempDB, splitting an
MDF to multiple files probably won't hurt but it also most likely won't help unless you can guarantee that those other files will be on a separate set of spindles with their own R/W heads. SANs usually don't work that way. The only thing that we've forced to separate spindles is large amounts of legacy data and we've moved that to less expensive (and slower) hardware.
To wit, "Spreading the I/O on the SAN fabric" will simply mean that the R/W heads need to move more times to read the same data and that can really slow you down. My recommendation is to spend the time and the money on fixing performance challenge queries instead of messing around with the SAN. You can usually get a 10-60X and sometimes a 100X+ performance improvement by fixing problematic queries and adding the correct indexes. Messing with the SAN will be aggrevating and problem net you only a 25-50% (1/4-1/2X) improvement... that's IF
you don't actually decrease the performance by messing with the SAN (which is most likely).
Even splitting TempDB out to a separate set of spindles will only help a little bit compared to the massive performance improvements you can get by fixing performance challenged code (and, sometimes, DB Design).
Hardware helps but the real performance is in the code.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs