I'm fairly new at Microsoft SQL. I've seen many tutorials online on how to add a file to the database. What I still don't get is why?


You could

  1. put huge archive data onto a big but slow drive
  2. put structural information ("meta data") into its own file
  3. put indexes etc. on a fast drive
  4. put large readonly data (e.g. old legacy data) onto a SSD
  5. set up a more intelligent backup strategy

I'm sure there's still more to add :-)

  • It can also be useful for huge database transfers : if you have one big file to transfer/restore in a maintenance window... It may once be too big to fit into this window. If you have multiple files, you can use the principle of divide and conquer. – Jefferson B. Elias Oct 19 '15 at 20:48

There's several reasons one could and should do this. One of the less discussed items is logical file contention within the MDF/NDF file. When you have a lot of processes trying to access data in that single file it becomes a logical point of contention which will add lots of PAGE_LATCH wait times (not page_latch_io which is physical contention). See here for more info.

As your database grows you might want to spread the files to different drives for expandability. Of course, SQL Server uses a round robin proportional fill algorithem so you'd have to be mindful that it will create a hot-spot until it's the same size as the others. You can read more info on that in BoL here.

Other old school reasons used to be to separate indexes so you improve writes. This has largely gone to the wayside due to flash storage and intelligent SANs but if you need to squeeze every last bit of write IOPS out of it, then this would help. Basically optimization.

Sometime's it's helpful to move data from old disks to new disks or if the old disks are showing bad sectors or such.

Those are some of the main reasons someone would add different files. The main reason I do it is to avoid logical file contention however in many environments I've worked in now that SSDs and cheap RAM are so readily available, personall.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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