May 16, 2008 at 4:18 pm
Currently, one of our databases uses only one data file (mdf). I am considering adding to it multiple secondary files (ndfs) in hopes to avoid contention. I would add 7 more files because we have 8 CPU (read from Microsoft it is best practice to have one file for each CPU). I am trying to visualize how data will be distributed once the new files are added. Would the new files be used until they are the same % full as the mdf and then it would start using the mdf again at that time?
I don't plan on creating a new filegroup -- they will all be on the same SAN array. (RAID 5)
Thank you in advance!
May 17, 2008 at 7:00 am
Yes, use multiple files! It has some advantages, even on a SAN. Because Windows is using a thread for each file, IO management will better perform. Also restoring a database with multiple files is much faster compared to one big file (parallel processing). If you're using multiple files, you can also spread this across multiple disks (if there's no SAN)
TIP: Check microsoft for doing this same trick for the TempDB (nr of files = CPU cores, all files equally sized)
If you create new datafiles, new data will be equally spread over these new files. Data in the original file will remain.
NOTE: do not check the option in Visual Studio "empty file by migrating the data to other files in the filegroup" if you want SQL server to spread all the data to the new files. You can't empty the first datafile, because it contains system objects.
FYI: just did this operation last night on a datawarehouse DB. I had one file 120 GB and I added 4 files from 32GB. After inserting new data the 4 files were all filled up to 24GB. The original file contains now a lot of free space, so I'll shrink this file until all my files are the same size.
If you want to move all your data to multiple files, you have to create a new filegroup (with files) and move all your data to this filegroup. There's a script for doing this.
Maybe it's an idea to create a separate filegroup (with files) for your indexes and put this on a separate volume. Scripts for moving indexes to new filegroups are available.
Wilfred
The best things in life are the simple things
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply