Post reply

Table partitioning with 2016 standard edition

  • October 31, 2017 at 10:23 am

    #326034

    I have never partitioned any table so I am not familiar with its pros and cons but just to test it out, I have 2016 standard edition installed in one of our test server and I am thinking about creating a partition (by date range) on one of the table which is 170GB in size. I have looked for videos on "How to create a partition while working with 2016 standard edition" but couldn't find any. Can someone guide me towards a right direction?

  • October 31, 2017 at 10:25 am

    #1966017

    Once you've got SP1, then partitioning is the same as for Enterprise edition.

    That said, why are you considering partitioning? What's your goal?

    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

  • October 31, 2017 at 10:42 am

    #1966019

    Why? I am not sure, I haven't read any article that says 'Partition does or doesn't improve performance". The idea is to enhance the performance. This table gets updated weekly where tens of millions rows are processed and the table also has multiple indexes so I am sure SQL Server is working as hard as possible by not only creating room for new records but also updating index pages as well. So why partition a table, to enhance the performance but this is just my theory.

  • October 31, 2017 at 11:23 am

    #1966027

    If you're after better performance, don't waste your time. Partitioning is not for performance, it's for data management, fast loads, fast deletes.

    If you want to be able to switch data in and out, great. If you want queries to run faster, it's unlikely to help there and may even make the queries run slower.

    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

  • October 31, 2017 at 11:26 am

    #1966028

    I would say its for data management then since millions and millions of rows are inserted every week.

  • October 31, 2017 at 11:38 am

    #1966031

    Then you're looking at a sliding window scenario. There's lots of documentation available on how to do that.

    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

  • November 1, 2017 at 3:11 am

    #1966130

    I haven't had to use partition-switching for some time but when I did I had to make sure I actually understood it.
    The results of that documentation are here.

    Steve Hall
    Linkedin
    Blog Site

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

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