February 3, 2006 at 1:59 am
Hi,
I am in the process to tune my application database using SQL2k
to use filegroup feature as the size is growing daily to ease
future maintenance like backup and performance issue.
Anyone got any experience on what to look out for
or any advise/comments for me on using the
filegroup features of SQL2k.
Any help/comments is very much appreciated. Thank you
February 3, 2006 at 8:06 am
It makes administration harder. I've only used it when I had data for archiving I could separate out into it's own filegroup. Or when I had disk constraints.
How much is the growth? And is it eating up all your disk?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 4, 2006 at 6:02 pm
Hi,
Yes, I read on some articles that mentioned it will add extra administrative work
on the maintenance and backup/restore.
The growth is around 10-20G in 3-6 months time. I am afraid that the size will
be so big that we are having problem transfering it to my developer size for
any debugging use. We are having have some performance issue which we hope this
feature will help a bit. In addition, we also like to use this feature for our
expiry records to improve some query performance.
Please advise on the best way to implement this feature and what
to look out for. Thank you
February 7, 2006 at 7:56 am
The best use for filegroups is when you can separate filegroups into individual disks for improved perfomance. Also, you can put your most critical filegroups on your fastest disk while the less-critical (archived) data sits on less expensive (slower) disk. This does make administration more difficult but typically only if you want to. You can do individual filegroup backups/restores however if you stick to full and transacton log backups the backup/restore procedure is practically identical. In SQL 2005 there is a much more compelling reason to use multiple filegroups because of the online restore/recovery operations.
February 7, 2006 at 12:13 pm
Be aware that filegroup backups will only work when your recovery model is FULL.
Otherwise, filegroups are mainly used to control placement of objects by putting them on specific disks, either for space or performance reasons. Typical performance optimizations utilizing filegroups include placing a table on one drive, and its non-clustered indexes on another, or placing two or more large tables participating in a join on different drives. Whether or not these kinds of optimizations are worthwhile can usually only be determined by actually trying them. I have seen performance improvements of about 30% when joining two large tables to populate a third, by placing them all on different drives, but in general, do not expect miracles.
Filegroups can also be used to distribute data across multiple drives, but in large production environments this is more commonly accomplished by RAID striping. When filegroups are used for this purpose, be careful not to include drives where space is limited, even when using the MAXSIZE parameter. If you create an object on such a filegroup, the server may decide to place the object on the size-limited file. If this object grows to consume all the space in that particular file, you might get a 'filegroup is full' messsage, even though there is plenty of space in the other files.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply