July 29, 2008 at 5:37 am
Whats are the Advantages of Filegroups in Sql Server 2005 ?
July 29, 2008 at 8:07 am
You can associate a table or an index with a filegroup. This allows you to control placement on various disks, setting groups to be read only, backing up by group. You can take a group offline in certain circumstances... Those are the general ones.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2008 at 2:21 pm
If, for example, you place the filegroups on different disks, and then place tables on one and indexes on another, you can do parallel writes/reads, which can give you greater speed. Other arrangements with some tables in one filegroup and others in another can give similar advantages.
Note that this isn't as easy to set up correctly as it may look at first glance. It takes some research and some work to get it right.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 2:55 pm
if its a VLDB you can backup at the filegroup level.
I this ia for an exam question do yourself a favour and google filegroups, plus check out BOL and MSDN
---------------------------------------------------------------------
July 29, 2008 at 10:30 pm
My DB is gone upto 5GB and it has only .mdf and .ldf files and i was thinking to implement the filegroups logic , before doing so , i was thinking of its advantages , it the only advantage is to distribute the DB on different drives and i can increase the Drive space,
so why i need to implement filegroups on the DB ?
July 30, 2008 at 12:09 am
5GB is not that big.
It is recommended to keep .mdf and .ldf on separate drives.
July 30, 2008 at 3:47 am
Suresh B. (7/30/2008)
5GB is not that big.It is recommended to keep .mdf and .ldf on separate drives.
Suresh is correct. 5Gb is not big so no need to start splitting out filegroups for that reason. If you are looking for performance improvements best bet is put your data and log files on sperate drives.
Only exception to this is if database is read only (or almost exclusively so) then sperate your busiest indexes into a sperate filegroup on another drive instead.
If you are not suffering performance problems you don't need to go down the filegroup route yet.
---------------------------------------------------------------------
July 30, 2008 at 4:30 am
File groups are used to enhance the performance and also domain wise you can group the tables like consider am having a company database. Here employee related information can be stored in employee filegroup and operations related can be stored under operations filegroup..
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
July 30, 2008 at 5:49 am
Storing data into different operational areas doesn't add any actual benefits unless those different operational areas also have different loads, different stresses, needs to be backed up at different times... Simply using filegroups as a organizing mechanism for grouping tables is unnecessary overhead without a purposed benefit.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2008 at 7:15 am
domain wise you can group the tables
Schema can be used for this.
July 30, 2008 at 7:18 am
Good point on schemas. Although there, again, is a bunch of behavior that simply using it to group tables by function without also taking advantage of the security & execution options seems like a possible waste of time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2008 at 7:23 am
the same post here
http://www.sqlservercentral.com/Forums/Topic542536-146-1.aspx#bm542562
plz mods merge it!
July 30, 2008 at 7:30 am
Dugi (7/30/2008)
the same post herehttp://www.sqlservercentral.com/Forums/Topic542536-146-1.aspx#bm542562
plz mods merge it!
aaah....thats annoying................
---------------------------------------------------------------------
July 30, 2008 at 10:18 am
Venkatesan Prabu (7/30/2008)
File groups are used to enhance the performance and also domain wise you can group the tables like consider am having a company database. Here employee related information can be stored in employee filegroup and operations related can be stored under operations filegroup..
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/%5B/quote%5D
This will actually reduce performance if you commonly join tables from those two domains. Tables that are often joined to each other should be in the same filegroup, per BOL (or so I remember).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 31, 2008 at 1:17 am
I am already using the schema's to group the tables domain wise,
so what the filegroup does ??
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply