2

Is there a rule of thumb on how many data files (mdf, ndf) I should have for a database?

3 Answers 3

3

Tempdb seems to be a special animal as they address is specifically.

http://msdn.microsoft.com/en-us/library/ee377054%28BTS.10%29.aspx

Splitting the TempDB files across multiple files may resolve performance issues related to I/O operations. As a general guideline, create one file data file per processor and use the same size for all files created.

Most other databases should have their data and logs on different drives. These drives will most likely be a RAID configuration, so you are in fact using more than one drive behind the scenes.

If you have a hot table, you can move that to another filegroup and put that on a separate disk group.

A certain performance monitoring tool I have been using makes recommendations to split databases into files that equal the number of processors on the machine. I don't remember reading that in any MS documentation though.

1

Generally, 1. The one-file-per-processor thing, aside from TempDB, I think is a myth with modern versions of SQL Server.

There are only a few very unique scenarios where multiple files give any real advantage (partitioned tables in a very large data warehouse using piecemeal backup is one example).

In terms of disk performance, it's usually better to have more disks striped in a single logical unit, and one big data file spanning all of them, because you get the performance advantage of all the disks for all the activity in the DB. If you had the same number of physical disks, but instead broke them into smaller groups, and put the data in specific files in each, then in most cases you've reduced net performance, not increased it.

0

as a rule of thumb you should use at maximum 50% of your disks (or disk-systems) capacity otherwise you are at the point to split your mdf file following the rule of the 50% limit.

hope this simple rule guides you.

Ice

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .