THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Fully transparent table partitioning for OLTP

No, this is not something announced at PASS Summit 2011. But it’s something that has been on my wish list for a long time.

Table partitioning is a great feature, and overwhelmingly, table partitioning is touted as a data warehousing feature for both sliding a partition into a table as a means of loading new data quickly and for sliding a partition out of a table as a means of purging old data quickly.

The pattern of the sliding-window data access, however, is not limited to data warehousing applications. For many OLTP applications, new data is constantly being inserted into a table from the upstream systems (e.g. mid-tier application servers). The focus of the application is mostly on the more recent data, and the older data in the table may be purged or archived to somewhere else. For an OLTP table, we probably would never slide any pre-populated partitions into the table. But we could benefit greatly from being able to slide a partition out of the table, relieving us of having to purge old data with a series of DELETE statements, a slow and painful process.

In the real-world, the table partitioning feature is not widely used on OLTP tables, at least, not on the systems I’m aware of. The main deterrent seems to be the amount of maintenance overhead in managing new partitions.

Come to think about the table partitioning feature. It is really a physical level concept. From the application or logical level, we want to have its benefits, but rarely if ever do we want to go through the laborious process in maintaining it. In other words, we’d like the feature to be transparent at the application and logical level.

For instance, why can’t we just say something like:


And expect the SQL Server engine to do the right thing in creating the partitions as necessary, and put data into the correct partitions. As long as we can completely express our intent in how the data should be partitioned in that table, we really don’t care how SQL Server does it internally as long as all the other table operations continue to behave as is. Of course, we’d like to be able to execute:


And expect the partition for that week be dropped as a metadata operation.

As a new week approaches, SQL Server knows that it needs a new partition to accommodate the data coming into the table, and will just automatically create one under the hood without bothering the human user. That’s total transparency, and would make the life a lot easier when it comes to purge old data from an OLTP table.

Yes, I know you can automate the process of managing new partitions with a script and you can probably pre-create enough partitions to be sufficiently safe. But building that transparency into the database engine as a feature relieves the user from having to worry about how robust our scripted automation may be.

Published Monday, October 17, 2011 3:27 PM by Linchi Shea
Filed under:



Jimmy said:

That would be awesome.  Today we're having to roll our own custom script which is just a pain in the butt.

October 17, 2011 3:52 PM

AaronBertrand said:

WEEK in particular is problematic, since this means different things to different people in different parts of the world, and would have to be based on something user-defined (or something even more volatile, such as DATEFIRST).

October 17, 2011 6:18 PM

Linchi Shea said:

Maybe I shouldn't have used Week as an example, but you get the drift.

October 17, 2011 7:32 PM

Jason said:

Coming from Oracle 8i, I'm quite surprised this DDL functionality does not exist to drop old partitions and prepare new ones.

October 17, 2011 10:10 PM

Linchi Shea said:


Table partitioning does exist, if that's what you are referring to. It's just that everything is manual and you have to make sure that a partition is there when you need it. Managing all those partitions manually can be a hassle. Does Oracle do this automatically?

October 17, 2011 11:16 PM

LeoPasta said:

Completely agree, Linchi. For the record the system I help develop (OLTP for CreditCard fraud detection) uses partitioning exactly to minimize the cost of deleting old data.

The largest part of our partitioning development was spent writing code to ensure there "new partitions" are created in advance, it is not hard to code it, but then you need to code (or cope) with scenarios like if our maintenance routine fails to execute (if SQL Agent is down for example). As you said, for some scenarios this could quite easily be built-in the product.

But what I don't understand is why on earth do I need to create a "replica" of my table, switch it out, just to drop it at the end. Was it too hard to implement "ALTER TABLE y DROP PARTITION x"?

October 18, 2011 5:01 AM

GrumpyOldDBA said:

100% in agreement with you here. I've used partitioning in OLTP databases since SQL 2000 ( views here ) but all you ever hear or see is examples for data warehouses.

I'd also like sub partitions or partitioned partitions if you like, at the moment you can only do this by using a partitioned view of partitioned tables.

And yes I agree with LeoPasta all this switching is quite time consuming to automate.

October 18, 2011 6:41 AM

krishna said:

i am also looking for the easy partition funtionality for last 10 years as mentioned by linchi.but still its complicated ..

October 24, 2011 6:01 PM

jeff_yao said:

Completely agree with LeoPasta. In my work, I have to dynamically create a "replica" of my partition table, including all indexes, and then do the switch out to the "replica" and then drop the partition. I believe the sql server product design team is too busy with big ideas they thought useful to the users but just ignore these small yet very important usability features.

November 15, 2011 3:34 PM

si said:

The SQL dev team aren't really users, so determining usability benefits is often difficult. To influence this, file a connect issue and get as many people as possible to vote on it. Things like this need visibility. Usability isn't great in so many areas, when it could be so much better with a bit of investment - the dev group have finite resources and need to work out how many more copies of x edition they could sell if a feature was developed or improved. I'm sure they'd love to enhance many areas but business priorities come first, hence show them it's important to a large customer base and it'll have an effect on prioritisation.

December 11, 2011 8:14 AM

KC said:

Partitioning OLTP tables is catching on in the following (2) areas:

1.) Archiving/Performance - Partitioning tables to eliminate archiving process involved in multiple "year" dated tables to hold the same information. Also this can be used to move older data to less expensive hardware and free-up more expensive/faster SAN space for current data.

2.) Hash Partitioning for high insert tables to eliminate page contention.

July 1, 2013 6:09 PM

KC said:

Linchi your suggestions are exactly what's needed. It would be interesting to see what the performance improvement could be if every table was automatically partitioned with some form of Hash partitioning for the current day for maximum insert performance that would then merge the data into the correct date partitions and all of it automatically controlled to eliminate the current sliding window scripting mess.

July 1, 2013 6:50 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement