Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jan 3 '11 at 22:41

This question came from our site for professional and enthusiast programmers.

    
Additional Comment: When i said balance I/O I probably meant 'enable parallel operations' as well. And I am assuming in this scenario that I have one RAID array on volume R: where all files will sit and not a raid array per file in the file group! Assume the tempdb and transaction log are on their own raid arrays and this is just talking about the MDF/NDF –  GilesDMiddleton Jan 3 '11 at 15:31
    
Belongs on serverfault. However, the main reason to split the database amongst multiple files is to have those files sit on individual arrays. If they are on a single array or a single hard drive then you aren't gaining much, if anything. –  Chris Lively Jan 3 '11 at 15:58
    
I didn't even know Server Fault exists. Seems weird I have to start again with my community score etc on a different site when there are lots of sql server questions here already. Not sure I want to post in N different places. is there a central stack overflow portal that links all stack overflow sites together? –  GilesDMiddleton Jan 3 '11 at 16:15

1 Answer 1

up vote 4 down vote accepted

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

Edit: you don't often need the whole DB in RAM and when you need to read it should hang around in cache until evicted.

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.

Edit: 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?

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

share|improve this answer
    
Generally helpful, but if (and I haven't) you have a 1TB database, you're not going to have 1TB+ of RAM, so at somepoint you will have to go to disk to read data. –  GilesDMiddleton Jan 3 '11 at 18:08
    
Also, wouldn't I be best to use RAID and hotswapping for recoverability instead of splitting? Or is it more an alternative to that strategy. –  GilesDMiddleton Jan 3 '11 at 18:09
    
See my updates. If you think you don't need backups, or RAID provides enough recoverability, go for it. –  gbn Jan 3 '11 at 18:17
    
So multiple NDF files are really only then about managing size and helping recovery, and/or - performance in the absence of RAID. However, there is another thing to consider, that is the number of cores of the server. There should be a 1-1 mapping of temp DB's to cores and data files to cores. It all gets a bit confusing as some of dell's deployment guides say 1/2 the number of cores, and in this guide there's an array for each file per core, but only one tempdb array. dell.com/downloads/global/power/ps4q07-20070555-Chen.pdf –  GilesDMiddleton Jan 3 '11 at 20:51
    
Actually, re-reading it, for MDF/NDF - The number of arrays = number of sockets, and with the number of data files residing in each array should equal the number of cores per socket. And just in the array for tempdb, the number of data files for the number of cores with a good preallocated size and 10% growth. –  GilesDMiddleton Jan 3 '11 at 20:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.