Does it increase performance to use multiple datafiles for large databases

If you've large databases (above 200GB/file). Does it help to have multiple smaller files (For e.g four 50GB files)? Does it improve performance. Also if a single table (size 200gb) is alloted to a file group which has one datafile. Does it help to have such filegroup with multiple files (fore e.g 4 files of 50GB each). My thought was if SQL Server is looking for certain data (assuming the file is not very fragmented) it is better for SQL Server to load up only the specific file that corresponds to the data, instead of loading up an entire 200gb file.

Moving to engine forum|||In general, the size of the data files does not really matter that much, as SQL Server will not load an entire file. SQL Server uses an offset in a file to go to a particular page (8K chunk of data) that it needs to load.

What really matters is the placement of files on disks, and the type of disks you use. If you have a single large file that is backed up by a RAID array, it does not really matter much. However, if you use ordinary (non-RAID) disks, multiple files might help, as you can place different files on different disk. Especially, placing the non-clustered indexes on different disks than clustered indexes will help.

Also, placing tempdb on (one or more) different disks, and placing the log files on different disks will help performance.

If you need more information, could you explain the problem you are trying to solve in a litte bit more detail.

Thanks,

Marcel van der Holst
[MSFT]|||

A lot of this depends on what kind of storage you have, whether is direct attached storage or a SAN, and if its a SAN, what kind of SAN it is. Generally speaking, Microsoft recommends that you have one TempDB file per physical CPU (and dual-core CPUs count as physical CPUs here), and one data file for each physical CPU. Doing this can help with allocation bottlenecks. Some SANs (such as 3PAR) make this somewhat moot, since they spread a file among all of the physical disks. Having multiple small data files can make backups and restores easier to manage.

You want your data files on one set of disks, log files on another set of disks, and TempDB on another set of disks.