Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table partition - Multiple filegroups vs Primary filegroup?


Table partition - Multiple filegroups vs Primary filegroup?

Author
Message
Tarun Jaggi
Tarun Jaggi
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 698
Hi All,

Can someone tell me the advantage of doing the table partition on multiple filegroups or on primary filegroup?

Thanks in advance!!
2ndHelping
2ndHelping
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 51
It's not a best practice to create user tables in the PRIMARY filegroup to begin with. Leave PRIMARY just for the system catalog, create at least one new filegroup for your user data, and then make one of the new filegroups the default for safety's sake. Now as far as partitioning goes, you need at least as many filegroups as the number of partitions your partition function would create.
georgesquared
georgesquared
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 47
See http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx for a good description. The MS white paper http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/PartTableAndIndexStrat.docx also has very good information.

In a nutshell,
-- Multiple file groups allow you to better split up & load balance I/O activity across multiple physical disks, especially if you set up each file group on a different disk drive.
-- Multiple files allow parallel activity at the Windows level.
-- File groups work at the SQL Server level. They aid in managing your physical space more easily.
-- Using different disk drives for each file allow parallelism at the hardware level (multiple spindles spinning and accessing data).

I like setting up a one file group for each partition. This makes management a little easier for me, since a partition & a file group are synonymous, and this way you can spread a query across multiple physical disk spindles.

Even if you don't have enough physical drives to allocate one file group/partition per drive, set up one file group for each partition anyway, and if needed, I'll double up the least heavily used partitons/filegroups, but try and allocate the most frequently used "hot spots" to their own dedicated phyical disks.

Hope that helps.

G**2
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
George is pretty much dead on the money, but a comment about multiple partitions in the primary.

The only time I would recommend using partitions without multiple file groups is for partition swapping.

Not that partitioning can't still help you, particularly if you can alter your queries to make sure that most calls stay in a particular division of the partition. It is, however, mostly just an administrative tool at that point rather than an optimizing one, as you could get equivalent performance from the partition optimization (within reason) with just a more effective indexing mechanic.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
techlady
techlady
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 23
does any one have example how to create new filegroup for each new partition ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search