Share via

Best Practice to Decide Partition Table in SQL Server

Arief Hardiansyah 51 Reputation points
2022-10-13T03:39:13.957+00:00

Hi All,

We have a database on SQL server with a large size (about 20 TB) and we plan to partition the existing tables in the database.
but we are still confused to determine whether to partition the table, whether it should be partitioned weekly, monthly, or yearly.
is there any best practice given by Microsoft to determine this? or does anyone have experience with this?

Really appreciate for your answer.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,344 questions
Developer technologies | Transact-SQL
SQL Server | Other
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 47,436 Reputation points
    2022-10-13T05:05:46.737+00:00

    is there any best practice given by Microsoft

    There is not really a best practice, because the decision about how to partition a table highly depends on the table design, it's content and the most common queries agains the table.
    But there is a guide for how to decide it: Data partitioning guidance

  2. YufeiShao-msft 7,146 Reputation points
    2022-10-13T08:21:01.46+00:00

    Hi @Arief Hardiansyah ，

    one of the many best practices for SQL Server's table partitioning feature is to create extra empty partitions around your data, one of the biggest benefits to table partitioning is the ability to move a lot of data in or out of the table very quickly
    How To Decide if You Should Use Table Partitioning

    You can refer to this blog: Table Partitioning Best Practices

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  3. Tom Phillips 17,771 Reputation points
    2022-10-13T13:58:41.287+00:00

    It completely depends on how you use the tables.

    There are 2 main benefits of partitioning:

    1. Partition elimination on queries
    2. Partition swapin/out for data loading/purging

    For partition elimination, the partition must be included in your query condition.

    See:
    https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16

    If you are not using these features, then you will not get any benefit from partitioning, and I recommend against it.

    I do not ever recommend partition by less than a year. This becomes a maintenance nightmare when you have 100s of partitions.

  4. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2022-10-13T21:42:32.843+00:00

    As others have said, what you should partition by depends on your data, and how you intend to use the partitions. If you age out data monthly, then go by months. If you age out data by week, go by week. If you don't age out old data but keep - maybe you should not partition at all.

    If you partition on a column which is typically not present in your queries, you will see a drop in performance.

    And say this: say that you have Orders and OrderDetails. You can partition Orders on OrderDate, but there is little use with adding OrderDate to OrderDetails. Rather, you would partition on OrderID. Which means that you cannot create partitions until it's time for a new one. Certainly possible, but quite a bit of work to get going.

    I would recommend that you figure out what you want to achieve with your partitioning.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.