November 18, 2006 at 10:24 am
Hi,
I need some advise. I maintain a small database and the data files were stored locally on the server. When database grew larger than the disk, we just added a new drive and added a new file to the database. Now, we are moving to SAN and I would like things to be clean.
Question...
1. Is it possible to merge the two file groups without creating a new database and exporting data?
2. The database will grow tremendously in the next few months. Is it better to keep the 2 file groups separate for performace purpose? Does it matter if you have 2 or 3 smaller file groups or 1 large filegroup?
Any help will be appreciated.
Eric
November 19, 2006 at 9:29 pm
They are not easy questions. Here are my inputs.
1. I think you have to create a new database. Then, export data. Otherwise, you have to merge the two file groups first, which become impossible in turn because of your disk space.
2. I assume your two file groups are data files. Therefore, the answer for this question really depends on the way you group your data.
November 20, 2006 at 8:05 am
Alright…here is some more detail and a correction to my original post.
The correction is that the database actually has 1 filegroup which is spread over 2 disks. Not 2 filegroups as originally mentioned.
Furthermore, I now have 4 times the space of the data files so space is not a concern. I can have multiple copies of the database. I am not very comfortable with creating a new database with one filegroup and importing data into it because it may slow down people from using the database. If there is a faster way to merge the files like you mentioned in your point # 1, I would love to know how.
Now that I have corrected my post that there is only 1 filegroup spread over 2 drive, do we get any performance enhancement if we merge the two? If not, I’ll just leave it the way it is and just expand the drives.
Thanks
Eric
November 20, 2006 at 8:48 am
So instead of merging filegroups, you need to merge datafiles within a filegroup? If so, make sure your target datafile is set to autogrow, then run a DBCC SHRINKFILE <FileName to Merge From>, EMPTYFILE. This will empty the contents of the second data file into any other available data file within the filegroup. Look DBCC SHRINKFILE up in BOL for exact syntax.
November 20, 2006 at 11:42 am
Both performance and backup may benifit from multiple file groups. But it may not true vice versus. As in my previous post, you may know the purpose why you have two file groups. If your server cannot benifit from multiple file groups, I would like to keep configuration simple, i.e., using one file group.
November 20, 2006 at 2:42 pm
Well…I was thinking along the same idea that John Rowan suggested but I also wanted to take SQL Oracle’s suggestion into consideration. However, I do want to make it clear that I have 1 filegroup on two drives…NOT two filegroups on 2 drives. The reason the filegroup is on 2 drives is because of drive space restrictions that we had at one time. Now, we want to merge it.
Keeping that in mind…will if affect performance if I merge the 2 datafiles (provided that they both belong to the same filegroup)? Please note that this database will grow over a terabyte.
I think we have a good discussion going. I know my options now…just wanted to make sure that performance will not be affected.
Thanks
Eric
November 20, 2006 at 3:04 pm
Well, there are a few things to consider here. As SQL Oracle stated, using filegroups can help you improve performance. This is done by spreading out I/O operations over multiple disks and thereby reducing contention. In order to achive this, you must first decide which tables and indexes you want to separate out based on how your application uses those tables and indexes. Once you’ve identified which combinations of tables/indexes you want to place in each filegroup, you then create each filegroup on its own disks/RAID. Again, the advantage you get in terms of performance comes from spreading out the I/O over multiple disks/RAID arrays.
When you add a file into a filegroup as you have already done, you are in effect simply extending the size of your filegroup. Since you’ve done this and given each file its own disk, you may have unintentionally helped with performance. The down side to this is that you cannot explicitly tell SQL Server which file to place certain objects on. So there is a chance that SQL Server has placed some of your heavy hitter tables/indexes on separate files, but this could change over time as you rebuild indexes, purge data, etc.
The only way to guarantee that you are spreading out your I/O is to use filegroups. With a database of your size, it may be a good idea to use filegroups as long as you have the disk space. You mentioned that you are moving to a SAN. You will need to make sure that the SAN administrator knows that the LUNs that he/she is allocating for your database files and logs should not share physical disk/spindles with LUNs from any other application/fileserver/etc. This is very important! Many SAN administrators do not realize the negative impact that sharing disks/spindles has on a database server’s performance.
November 20, 2006 at 8:56 pm
Thanks for all the replies. I think I now have a better idea of what to do.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply