Does multiple files in SQL Server when using RAID help reduce conflicts in growth and file-locking?
- by Dr Giles M
I've been reading around and get the impression that if you are using RAID then using multiple SQL Server files within a filegroup won't yeild any more improvements, and the benefits are purely administrative (if you started to run out of space or wanted to partition off data into managable chunks for backups/balancing the data around your big server room).
However, being a reasonably savvy software person, it's not unthinkable to hypothesise that, even for smaller databases that SQL Server will perform growth and locking operations (for writes) on a LOGICAL file basis, so even if you are using RAID, it seems to make sense to have multiple files in a file group to balance I/O, 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?