October 3, 2006 at 1:41 pm
Would there be any benefit to creating a secondary data file on a large database (approx 235 GB)? I'm not thinking of creating a file group and moving tables or indexes because we can't afford the down time. If I limit the growth of the current data file and let the second one start filling up, would we see any performance difference? Performance isn't bad now, but it could be if the database continues on its current rate. Also, would database backups run faster or the same?
Anyone have any thoughts on this? I don't have a test box with enough disk space to test....
Thanks.
October 3, 2006 at 3:12 pm
Secondary FG with multiple files will speed reads and writes as long as we're not talking RAID 5 here.
Downtime is a need for the operation. I have never added files to the PRIMARY FG though.
SQL uses proportional fill and you will gain the most having one file per CPU
Good luck
* Noel
October 4, 2006 at 8:59 am
Well, since we have RAID 5 I may need to wait. Our server is going off lease next year and we won't be using it (thankfully). Also, next year we are hoping to upgrade to SQL 2005 so that I can use table partitionng, but I'm afraid the tables will be pretty huge by then. Just trying to think of ways to optimize now because the upgrade is only tentative. Any suggestions are greatly appreciated.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply