We found modest performance gains with one of our tables following partitioning. This was before my time so it's anecdotal. Where we found significant gains was with batch loading tons of rows. Here's some details of the table:
704 GB data
685 GB index
85 partitions, one per month, about 200M rows each
Indexes are partition aligned.
About 50 million rows loaded per week in one batch. 95% of those new rows belong in the "current" partition.
SQL Server 2012, Enterprise edition, 768GB RAM but only one storage area serviced by one channel of one controller.
It used to take about 8-10 hours to load those 50 million rows each Saturday. I changed the process a few months ago so it uses partition switching and it now takes about 1.5-2 hours.
Partition switching also made it much easier to introduce data compression to the table.
The most significant perf improvement followed the creation of an aligned columnstore index.
[font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]
For fast, accurate and documented assistance in answering your questions, please read this article
Understanding and using APPLY, (I)[/url] and
(II)[/url] Paul White
Hidden RBAR: Triangular Joins[/url] /
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden