Press "Enter" to skip to content

Category: Partitioning

Partitioning Thoughts

Published 2016-02-03 by Kevin Feasel

Kendra Little has a few questions to ask before you set up sliding-window partitioning:

Map this out before you write the code. When will the jobs run, and what should happen if they fail? Should someone be engaged? What tools will they need, and when is the Service Level Agreement for when the process has to be complete? You’ll need lots of details on this to make sure your automation and documentation meet the bar.

Partitioning is an extra layer of complexity.  It can be a very useful extra layer of complexity, but this is a case where it’s best to spend an hour before you begin and walk through potential issues.  Those potential issues will come—automation jobs will fail, external configuration changes will affect your partition strategy, bad data will sneak in and fill up your supposedly-empty edge partitions.

Comments closed

Rebuilding Indexes By Partition

Published 2016-02-01 by Kevin Feasel

Andrea Allred shows how to rebuild indexes one partition at a time:

We have had an index job that has been failing for a while.  This is one of those things you really don’t want to clean up because no one is complaining, but you know you should.  I had heard that I could rebuild one partition at a time, but where to start?  Today, I worked my way through it, so here it is so that you can do it too.

First you need to find the biggest indexes, there is a good chance those are the ones that live on partitions. I am removing Primary Keys.

This can be a real time-saver if a majority of your partitions either are read-only or at least rarely update.

Comments closed

Fixing Partition Table Boundary Points

Published 2016-01-27 by Kevin Feasel

Kendra Little shows us how to fix a common partitioning issue:

Everything was fine, but we forgot to put a boundary point in for Jan 1, 2016… and now it’s well into January. All our data for January 2016 is in the partition with the December data.

The more time passes, the more data is going to go into that partition, and it’ll get bigger and more lopsided. If we’re switching out old partitions by month, eventually that’s not going to work. And partition elimination won’t work for anything after Dec 1, 2015, either!

Be sure to keep those empty partitions around, just in case .

Comments closed

Partitioning On The Cheap

Published 2015-12-07 by Kevin Feasel

Aaron Bertrand shows us how to partition on the cheap:

The TL;DR of this is that you can use filtered indexes to keep all of your “hot data” in a separate physical structure, and even on separate underlying hardware (you may have a fast SSD or PCIe drive available, but it can’t hold the whole table).

Using filtered indexes is an interesting way of solving issues that partitioning normally would help you out with.  I’m looking forward to the next part in the series.

Comments closed

Calculating Partition Sizes

Published 2015-11-25 by Kevin Feasel

Rolf Tesmer has a nice series on partitioning going. His latest entry involves calculating partition sizes in advance:

Sometimes (just sometimes) you need to calculate the size your table partitions upfrontbefore you actually go to the pain and effort of partitioning (or repartition) a table.  Doing this helps with pre-sizing the database files in advance instead of having them auto-grow many many times over in small increments as you cut data over into the partitions.

Check out the entire series.

Comments closed