locked
Partitioning best practice RRS feed

  • Question

  • Hi everybody,

    I'm working for a client to enhance his T-SQL jobs performance.
    I have to partition several tables (daily and monthly partitions).
    Do I need to create a partition scheme and a partition function for each table or may I use the same ones for all the tables which have the same granularity?

    Thanks for your help

    Pete
    Thursday, March 23, 2017 8:28 PM

Answers

  • I suggest reading this partitioning article:

    Table Partitioning Best Practices

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, March 23, 2017 9:33 PM
  • Do I need to create a partition scheme and a partition function for each table or may I use the same ones for all the tables which have the same granularity?

    You mentioned jobs and temporal partitioning so I'll assume you're planning an incremental or sliding window, along with SWITCH for efficient data load and purge. Partitioning will not necessarily improve query performance and can even degrade performance depending on query particulars.

    If partition boundaries and filegroups are identical and SPLIT/MERGE operations always done at the same time, you can use the same partition function/scheme for different tables and indexes. You'll need separate functions and schemes if you anticipate ever needing to split/merge a given table independently of the others. I usually share partition objects only when tables are closely related and maintained at the same time. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, March 23, 2017 10:48 PM

All replies

  • I suggest reading this partitioning article:

    Table Partitioning Best Practices

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, March 23, 2017 9:33 PM
  • Do I need to create a partition scheme and a partition function for each table or may I use the same ones for all the tables which have the same granularity?

    You mentioned jobs and temporal partitioning so I'll assume you're planning an incremental or sliding window, along with SWITCH for efficient data load and purge. Partitioning will not necessarily improve query performance and can even degrade performance depending on query particulars.

    If partition boundaries and filegroups are identical and SPLIT/MERGE operations always done at the same time, you can use the same partition function/scheme for different tables and indexes. You'll need separate functions and schemes if you anticipate ever needing to split/merge a given table independently of the others. I usually share partition objects only when tables are closely related and maintained at the same time. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, March 23, 2017 10:48 PM