Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table partition - Multiple filegroups vs Primary filegroup? Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 2:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:08 AM
Points: 1,177, Visits: 571
Hi All,

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

Thanks in advance!!
Post #1363367
Posted Tuesday, September 25, 2012 8:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:58 AM
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.
Post #1364072
Posted Tuesday, September 25, 2012 10:09 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 8:34 AM
Points: 4, 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
Post #1364163
Posted Tuesday, September 25, 2012 1:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:44 AM
Points: 6,235, Visits: 7,371
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
Post #1364263
Posted Wednesday, October 23, 2013 7:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 4, 2013 1:34 PM
Points: 2, Visits: 23
does any one have example how to create new filegroup for each new partition ?
Post #1507617
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse