Merge Partitions in SQL Server 2008 R2

  • Hi,

    We have the following scenario on our company.

    There's a table which keeps 60 days of data and every day, there's a sliding window maintenance that merge the getdate()-60 partitions. This table is partitioned on a datetime column and each partition keeps 15 minutes of data. So, the sliding window has to merge a partition 96 times (4 partitions per hour times 24 hours in a day).

    Some facts:

    - All partitions are in the same filegroup

    - The function and scheme partitioning are shared for other 5 tables

    The issue is the merge partition takes a huge time to execute, even if we switch out the partitions that needs to be merged.

    Is there any best practices I am not following? Should I create a filegroup for each partition? Should I create a function and scheme partitioning for each table?

    I didn't find any helpful article or whitepaper on this.

    Thanks

  • You want to make sure that you are only performing a metadata operation and not a data move operation. There are best practices around what you are trying to do. I would start here: http://www.brentozar.com/sql/table-partitioning-resources/



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • All your partitions are in the same filegroup. What you are trying to achive with partitioning?

  • Manageability and performance, for both are doing OK.

  • Can you elaborate more about manageability ?

    About performance: all your partitions placed in the same filegroup. If you have 1-1 between FG and files, you can't achive performance gain because all partitions located on the same pfysical disk. If you have many files per filegroup and they are spread scross multiple physical disks, all your gain in performance could be achieved without partitioning, just by having multiple files per non-partitioned filegroup.

  • SQL Guy 1 (3/12/2014)


    Can you elaborate more about manageability ?

    About performance: all your partitions placed in the same filegroup. If you have 1-1 between FG and files, you can't achive performance gain because all partitions located on the same pfysical disk. If you have many files per filegroup and they are spread scross multiple physical disks, all your gain in performance could be achieved without partitioning, just by having multiple files per non-partitioned filegroup.

    Thats not strictly true, you "could" get a performance improvement through partition elimination.

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

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