Partition tables with 1 filegroup

Question

Partition tables with 1 filegroup

sp_who2

SSC-Addicted

Points: 446
More actions
September 9, 2016 at 12:48 pm

#317251

I would like to know if there is any performance gain if you DO NOT create separate filegroup for your partitions and you put all the Partition in PRIMARY

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's unlikely to be a performance gain from partition whether you put the partitions to one filegroup or to multiple. Partitioning is not a performance feature, it's for data management, maintenance, fast loads, fast deletes.

The 'one filegroup or multiple' is more to do with wanting to be able to restore part of the DB, or set some partitions read only than it is about performance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

sp_seeker (9/9/2016)
I would like to know if there is any performance gain if you DO NOT create separate filegroup for your partitions and you put all the Partition in PRIMARY

Generally speaking, performance is NOT a reason to partition. Frequently, performance actually suffers a bit. Good code played against a properly designed monolithic table with the correct indexes will usually outperform partitioned tables.

The reasons to partition are normally centered about reduced maintenance, sometimes to reduce backups, and to be able to almost instantly switch large amount of data into or out of the table.

{EDIT} I almost beat Gail to the punch on this one. She's just to quick for me. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)