I have a table with approximately 1.4 bn rows; currently just one partition - but I would like to split the data into multiple partitions. I haven't been using partitions before, so I am not quite sure how best practice is.

The table contains monthly financial information for our customers and will increase with approximately 35 million new rows each month. Each row will have a YearMonth_Id and a Customer_Id as well as a lot of columns containing financials.

I am thinking to do the partition by YearMonth_Id (maybe grouped by quarters?).

I'm no dba, just a finance analyst - but since we don't have qualified dba to maintain this table regularly, it comes down to me to do it; hence I want to have as little maintenece on the table as possible.

My question therefore is as follows. When working with partitions and incremental values (YearMonth_Id), how should I structure the partitions on the table? I read that usually you would leave an empty partition to the far right (if working with left range) that you can split each month (or quarter). But I am thinking of creating, say 12 empty partitions for future values, so the next time I need to do a split will be in a years time; so that the table is not dependent on me every month.

Is this a big no-no or would it be an okay way to go?

I know it is a long post to arrive at a simple question - but I hope it gave a better understanding of what I am after.

I am running SQL Server 2014.

  • Have you already read this answer? dba.stackexchange.com/questions/48011/… – SQL_Hacker Aug 14 '17 at 19:50
  • @SQL_Hacker: I have now ;-) Unfortunately I am working in a very delicate environment, and I am not able to (or authorized to) install third-party applications that have not been approved. – ssn Aug 14 '17 at 19:57
  • 1
    Sure, you can create partitions in advance. Just don't forget to create new ones before the last partition contains data. I'd be inclined to use monthly partitions but it probably doesn't matter much if you main purpose is manageability rather than expectations of improved performance. – Dan Guzman Aug 14 '17 at 20:03
  • @DanGuzman I was hoping for improved performance as well. I just didn't want to make too many partitions if I got the opposite effect of actually slowing things down. So you would partition by each month rather than quarters? – ssn Aug 14 '17 at 20:07
  • 1
    Partitions are really designed for management, and not performance. If you want to get partition elimination (for performance reasons), you'll need to ensure that the partition key is a part of your query. – Nic Aug 14 '17 at 20:12

Empty partitions have negligible effect on query performance, so having up to 12 empty partitions, one per month, should not be a problem.

Having said that, you could simply automate the creation of partitions, the way I detail in this answer. That means you'd only ever have a single empty partition, assuaging any fears about performance impacts from too many empty partitions.

Table partitioning is not a panacea for increasing performance and can result in lower performance than a non-partitioned table. The primary use-case for partitioning is to enable the easy swapping out of old data, and swapping in of new data, using the ALTER TABLE ... SWITCH syntax. This answer shows the advantages of using ALTER TABLE ... SWITCH to ease data management.

In order to get some performance improvement via partitioning you need to use the partitioning key in queries against the partitioned table. Queries that don't use the partitioning key in the WHERE clause or in a JOIN clause must inspect all partitions for rows that match the selection criteria.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.