September 24, 2012 at 2:42 am
Hi All,
Can someone tell me the advantage of doing the table partition on multiple filegroups or on primary filegroup?
Thanks in advance!!
September 25, 2012 at 8:18 am
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.
September 25, 2012 at 10:09 am
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
September 25, 2012 at 1:36 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 23, 2013 at 7:29 am
does any one have example how to create new filegroup for each new partition ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply