A blog by Jose Barreto, a member of the File Server team at Microsoft.
All messages posted to this blog are provided "AS IS" with no warranties, and confer no rights.
Information on unreleased products are subject to change without notice.
Dates related to unreleased products are estimates and are subject to change without notice.
The content of this site are personal opinions and might not represent the Microsoft Corporation view.
The information contained in this blog represents my view on the issues discussed as of the date of publication.
You should not consider older, out-of-date posts to reflect my current thoughts and opinions.
© Copyright 2004-2012 by Jose Barreto. All rights reserved.
Follow @josebarreto on Twitter for updates on new blog posts.
Q) If you set up a database with 3 files, will SQL Server fill the first file before using the other 2 or will it store data in all 3 files right from the beginning? Is it related to the initial size or the growth setting of the file? What if data is deleted? What if it’s three filegroups with one file each?
One of the main ideas behind using multiple data files is to allow SQL to work spread the data across the multiple files. All other things being equal, if you have a database with four data files stored in four disks instead of a single data file on a single disk, SQL will be able to read and write the data faster. The data will be spread across the multiple files and SQL will work with all the files.
You will see that, over time, the multiple files will end up with roughly the same size, unless you run out of disk space in that volume. It's true that you will get the best performance if you pre-allocate space instead of letting the files grow.
Most SANs, intelligent controllers and even Windows Server itself can implement the striping of the data in multiple disks at a lower level (this is usually called RAID 0), but if for some reason you can't leverage that, SQL can help with the multiple data file setup. You can also combine the options for further striping.
Filegroups, in the other hand, are used to allow you to control where specific objects in your database are stored. You could, for instance, have a table with historical data stored in a separate filegroup that sits on a slower disk. You can backup and restore file groups separately, too.
For more details, check http://msdn2.microsoft.com/en-us/library/ms187087.aspx