Re: Multiple Data Files and paralell IO - yes or no

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Peter Daniels (nospampedro_at_yahoo.com)
Date: 03/30/04 

Date: 30 Mar 2004 15:10:41 -0800

Sounds like the answer is no - mutiple data files in a single file
group on one RAID array will not give an IO perf benefit. I'm still
going to do some benchmarking, but thanks very much for your input.

I'd still love to here MS's official statement on this.

"Mike Kruchten" <mkruchten@fsisolutions.com> wrote in message news:<OePgmzmFEHA.2768@tk2msftngp13.phx.gbl>...
> The last answer I got, from someone working for Microsoft Consulting
> Services, was that there was no benefit to multiple files on the same disk
> volume. However if you have multiple volumes you MAY see improvements.
>
> It depends on the IO characteristics of your disk volumes. Example, if you
> had 20 spindles would it be better to create one large RAID volume or >1
> smaller volumes if they're all on the same controller? Additional testing
> and/or input from the hardware vendor would be needed. If you have multiple
> controllers/storage systems, then spreading multiple files across the
> controllers should give a benefit. Whether you need to do this would depend
> on your app.
>
> HTH,
> Mike Kruchten
>
>
> "Peter Daniels" <nospampedro@yahoo.com> wrote in message
> news:2fd8f155.0403291246.190893b6@posting.google.com...
> > I've looked at many of the threads in this group that discuss whether
> > or not multiple data files in a filegroup will improve IO performance.
> > Some say that the BOL is wrong and that you don't need additional
> > files to have SQL server use parallel IO (starting with SQL 2K).
> > Others say that it still helps even in SQL 2K. I'm planning on doing
> > some benchmarking, but thought I'd throw it out there one more time.
> >
> > What is Microsoft's stance on this? Should we or should we not use
> > multiple files in a filegroup. If we should, do we use the rule of
> > thumb of roughly 1 file per spindle in the RAID?
> >
> > Thanks!
> >
> > -Peter

Relevant Pages

  • Re: Multiple Data Files and paralell IO - yes or no
    ... was that there was no benefit to multiple files on the same disk ... It depends on the IO characteristics of your disk volumes. ... smaller volumes if they're all on the same controller? ... > or not multiple data files in a filegroup will improve IO performance. ...
    (microsoft.public.sqlserver.server)
  • Splitting one mdf to multiple ndf files
    ... I have an mdf which is about 8 GB. ... If I add files to the existing FileGroup will it then automatically split ... If not then do I have to create a new DB with multiple files and then ... a dba but got stuck with some dba tasks) can know how to split a DB onto ...
    (microsoft.public.sqlserver.server)