Advantages of multiple SQL Server files with a single RAID array
- by Dr Giles M
Originally posted on stack overflow, but re-worded.
Imagine the scenario : For a database I have RAID arrays R: (MDF) T: (transaction log) and of course shared transparent usage of X: (tempDB).
I've been reading around and get the impression that if you are using RAID then adding multiple SQL Server NDF files sitting on R: within a filegroup won't yeild any more improvements. Of course, adding another raid array S: and putting an NDF file on that would.
However, being a reasonably savvy software person, it's not unthinkable to hypothesise that, even for smaller MDFs sitting on one RAID array that SQL Server will perform growth and locking operations (for writes) on the MDF, so adding NDFs to the filegroup even if they sat on R: would distribute the locking operations and growth operations allowing more throughput?
Or does the time taken to reconstruct the data from distributed filegroups outweigh the benefits of reduced locking?
I'm also aware that the behaviour and benefits may be different for tables/indeces/log.
Is there a good site that distinguishes the benefits of multiple files when RAID is already in place?
