Use case for SQL server table partitioning

Posted in: Microsoft SQL Server, Technical Track

 

Often we are asked by our clients about Table Partitioning, and specifically, which tables will be good candidates to be partitioned?
Here are some of the main use cases for Table Partitioning:

  1. You have the Enterprise Edition of SQL Server 2005 or higher.
  2. The table contains or will contain more than 5-6 million rows and growing fast, or its size is growing by around 1GB per month or more.
  3. The FULL backup is taking too long or the backup file is too large and older data is not being updated (i.e.: users can only update data from the last 3 months).
  4. Data needs to be archived or purged on a regular basis, potentially the current archiving or deletion of data is causing blocks or deadlocks to other processes.
  5. There is a NOT NULL date column or another NOT NULL sequential column that the table can be partitioned upon.
  6. Better if most queries are including the partitioned column in WHERE clauses (i.e: between the date range).

When partitioning a table, here are few things to look into:

  1. Create a file and a filegroup per partition (even if the files are created in the same place). This way, it is easy to backup (i.e.: FILEGROUP backup), maintain and archive/purge.
  2. The best way to partition a table is by a date column because data is usually archived or purged by a date. If you do not have such a column, you may want to consider adding a column that will contain the current date/time when the row is created. This column can contain the default GETDATE(). Using an ID or a calculated column may cause too many headaches.
    If the application returns an error when adding the date column, consider using a view on top of the underlying partitioned table.
  3. Partitioning requires maintenance:
    1. To add files, filegroups and partitions on a regular basis and in advance.
    2. Monitor data growth and potentially modify the partition architecture (i.e.: move from a monthly partition to a weekly or daily partition).
    3. Archiving/purging partitions on a regular basis. Consider using a SWITCH partition for quick archiving and purging.
    4. You can defragment indexes per partition.
    5. Remember that statistics cannot be updated by partition, however, you may want to consider FILTERED INDEXES or FILTERED STATISTICS to avoid updating statistics on the entire table, as well as improving performance in specific cases.
  4. Consider using MAXDOP <> 1 on the instance level or for specific queries that span multiple partitions in order to take advantage of parallelism. Configure parallelism with caution.

Additional links:

There is a lot of information around SQL Server Table Partitioning. Here are some of the useful links:

SQL Server Database Partitioning Myths and Truths
Table Partitioning in SQL Server – The Basics
How To Decide if You Should Use Table Partitioning
Query Processing Enhancements on Partitioned Tables and Indexes

 

Discover more about our expertise in SQL Server.

email

Interested in working with Michelle? Schedule a tech call.

About the Author

Microsoft Data Olatform Architect
Michelle has 30 years in IT, and has been working with SQL Server for the past 20 years. She has designed methodologies that consist of documentation, utilities, and scripts to automate architecture, design, and performance tuning initiatives for her clients. Michelle is able to see the wider vision of her clients’ business. She is passionate about solving problems quickly and providing value to her clients. She speaks English, Hebrew, Spanish, and a bit of French.

3 Comments. Leave new

Great overview but, just to be sure, Item #1 (You have the Enterprise Edition of SQL Server 2005 or higher.) isn’t a prerequisite. Partitioned Views work just fine in both the Standard and Enterprise editions and are sometimes easier to manage than partitioned tables if you can tolerate only having 253 partitions (more than 21 years if you partition by month and more than 4 years if you partition by week).

Some of the advantages of partitioned views are that the underlying tables online and separately and stats can be updated on a single partition because it’s truly a single table per partition. Yes, they do have a different set of caveats but most are trivial to non-existent when deployed on such “WORM” tables as you suggest. And, you don’t actually have to worry about non-aligned indexes messing up the ability to “SWITCH” because each partition is a separate table. Just rebuild the view to include or exclude a table (takes the same time as a real SWITCH) to effectively switch in or out.

Reply
Michelle Gutzait
January 18, 2016 9:08 am

Jeff, great comment, thanks!
In this Blog post I was talking about Table Partitioning not Partitioned Views which are different features of SQL Server.
Maybe in my next Blog I should discuss Partitioned Views vs Table Partitioning :-)
Thanks again!

Reply
Erik T Nielsen
January 18, 2016 5:44 am

Please observe that if you use TOP, MAX or MIN on partitioned tables then you get very very bad performance due to table scan.
This problem was reported in 2008 but still not solved in SQL 2014!

https://support.microsoft.com/da-dk/kb/2965553

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *