0

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?

1
  • If you're using zero initialization for the DB files, this advantage is seen on any storage, not just the RAID scenario you noted above.
    – user3914
    Jan 3, 2011 at 16:42

1 Answer 1

2

I answered something similar recently "Multiple Data files and Multiple File Groups"

It's also fairly complex and difficult to distill into a concise answer. It helps by knowing how SQL Server accesses data: see "SQL Server 2000 I/O Basics" which is still valid

Read performance is governed by RAM. You shouldn't have to go to disk to read data: if you do, you don't have enough RAM. In ye olden days before 64 bit, if you had a 500 GB database you could only fit 64GB RAM so then you would split indexes out or some such

Of course, you don't often need the whole DB in RAM and when you need to read it should hang around in cache until evicted. But more RAM never goes wrong

Write performance is governed by the LDF volume. Look at write ahead logging in the article above

You do split database file for recoverability in case a volume fails. With a good backup/restore plan you can work with MDFs and LDFs separately.

Recoverability is more than RAID arrays. What if your disk controller fails? Are your disks all from the same vendor/batch/firmware? What if 2 disks fail in a RAID 5? A SQL Server cluster? Corrupt files?

If you have multiple files on the same volume, any read request will take longer than reading one bigger file, often contiguously. Which is why file server and database servers are usually built differently

You can't find a good site bcause it isn't done: as you noted, everyone tends to say "no" to multiple files in the same filegroup.

You gain more by partition alignment and formatting NTFS correctly with 64k clustersa

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .