Partitioning in sql server 2012

Question

Partitioning in sql server 2012

PJ_SQL

SSCertifiable

Points: 6703
More actions
February 5, 2016 at 3:33 pm

#307700

I have 800GB database.
Will partitioning help improve performance if partition table distribute data in same LUN but in different filegroups?
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 1

In order to improve performance first you have to identify a current bottleneck, and only then search for a solution, not the other way around. You don't want to blindly create partitions hoping that it will fix some performance issues.

Alex Suprun

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

Partitioning is not for performance.

Spreading data across multiple files may give a performance improvement if the files are on separate IO subsystems and the current bottleneck is IO.

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

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 3

"separate IO subsystems" does it mean multiple processors?

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

Err, no. It means exactly what I said, separate IO subsystems.

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: 1004362 More actions · Answer 5

PJ_SQL (2/8/2016)
"separate IO subsystems" does it mean multiple processors?

To add to what Gail said, it usually means separate disk "spindles" at the very least.

And I also agree. I've never seen partitioning help performance of code and have actually seen it slow things down a bit even if you achieve so-called partition elimination. A correctly indexed monolithic table and good code is the way to go. Partitioning is to save time on other things such as index maintenance and, sometimes, backups depending on the usage and type of table.

--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)