January 12, 2018 at 7:58 am
TheSQLGuru - Thursday, January 11, 2018 4:13 PMEric M Russell - Thursday, January 11, 2018 7:23 AMDoes anyone here besides me implement partitioning on indexes just for taking advantage of incremental reorgs and re-indexing? It depends on the access patterns and chosen partitioning key, but at least in my case only the latest couple of partitions are run hot with inserts and updates, and the older partitions are relatively static.I advise that to clients on Enterprise Edition of SQL Server (which is very few).
Not your non-Enterprise Edition clients on SQL Server 2016 SP1 or better? Is doing partitioning without the additional Enterprise Edition performance benefits (like parallelism) not worthwhile?
It's a serious question; I've never dealt with that previously Enterprise-only feature.
January 12, 2018 at 8:17 am
TheSQLGuru - Thursday, January 11, 2018 4:13 PMEric M Russell - Thursday, January 11, 2018 7:23 AMDoes anyone here besides me implement partitioning on indexes just for taking advantage of incremental reorgs and re-indexing? It depends on the access patterns and chosen partitioning key, but at least in my case only the latest couple of partitions are run hot with inserts and updates, and the older partitions are relatively static.I advise that to clients on Enterprise Edition of SQL Server (which is very few).
Despite working with SQL Server across two decades and a half dozen IT shops, I've had little exposure to Standard Edition. In my universe, even when there are Standard instances, they are typically used to host 3rd party ISV type applications on premises and contain small to medium (less than 100 GB) sized databases. Most of my development and DBA efforts are focused on Enterprise edition servers, because the ISV boxes are tucked away in a closet somewhere and off limits, at least in terms of altering the object model.
Starting with SQL Server 2016 SP1, all editions (Enterprise, Standard and even Express) support partitioning. However, I've seen blog posts that suggest that even with functional support for partitioning, non-Enterprise editions do not support some performance features like partition elimination and parallelism. Still, having indexes partitioned (even without the Enterprise bells and whistles) would allow support for maintenance operations like incremental partition level re-indexing, truncating, switching, etc. which can be useful even on medium sized databases if it means the regular maintenance window can be reduced by an order of magnitude.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 12, 2018 at 2:19 pm
Nadrek - Friday, January 12, 2018 7:58 AMTheSQLGuru - Thursday, January 11, 2018 4:13 PMEric M Russell - Thursday, January 11, 2018 7:23 AMDoes anyone here besides me implement partitioning on indexes just for taking advantage of incremental reorgs and re-indexing? It depends on the access patterns and chosen partitioning key, but at least in my case only the latest couple of partitions are run hot with inserts and updates, and the older partitions are relatively static.I advise that to clients on Enterprise Edition of SQL Server (which is very few).
Not your non-Enterprise Edition clients on SQL Server 2016 SP1 or better? Is doing partitioning without the additional Enterprise Edition performance benefits (like parallelism) not worthwhile?
It's a serious question; I've never dealt with that previously Enterprise-only feature.
1) Partitioning at all is very often the wrong solution to a problem, EE or not. Read Jeff Moden's stuff or just push the "I Believe" button.
2) You need to REALLY spend a LOT of time with ANY new SIGNIFICANT feature such as table partitioning before you consider doing anything with it. I have had to clean up SOOOO many messes at clients over the last 25 years that don't heed that advice!!!
3) Most of the previously-Enterprise-Edition-only stuff that SQL Server dropped down to Standard Edition with 2016 SP1 (and also in RTM 2017 IIRC) come with limitations, gotchas, provisos, caveats, etc. KNOW THEM and TEST THEM before you go down those paths in production. Having said that, there are some pretty damn AMAZING things you can do now in Standard Edition if you take the time to do them right!!!! November 16, 2016 was the 3rd most important day in SQL Server's history with Microsoft EVER, IMNSHO.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply