October 28, 2005 at 2:27 pm
Is there any size threshhold at which a database file (.mdf ) becomes inefficient and the database better served by adding another file (.ndf ). ?
October 28, 2005 at 4:53 pm
The addition of another ndf file will only be benefitted by putting that new file on a separate drive.
DB size is limited by the space on the disk. but at some point if the heads are bouncing around too much, you may want to spread the data over additional drives to reduce the disk IO.
October 29, 2005 at 3:53 am
I always create my databases with mutiple files for two reasons.
1. If i need to move a file off onto a secondary disk array for performance it will be proportionally filled already and since here is no nice tool for redistributing data over mutliple files in SQL Server this makes life much simpler. Also because of the way the round robin wriritng works if just add a new file later on you'll probably find the nice new empty file is the only one that ends up being written to.
2. Also if you have nice new box with quite a few processors you should have as many files as you have processors. This is because you can only write to one file with one thread. So if you have multiple processors and only one file then only one of them will be able to write data to disk. To see if this is a problem monitor your server and see if only one processor seems to be doing all the work while the rest are having a nap. This can have a significant hit on performance as you don't want anything waiting on disk I/O unecessarily.
Anyway this nice microsoft bloke explains it better on his blog
http://blogs.technet.com/mat_stephen/archive/2005/02/02/365325.aspx
hth
David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply