Month: April 2015

Rebuild a Standard Table to a Partitioned Table

Mr. Fox SQL (Rolf Tesmer)16 Comments

[read this post on Mr. Fox SQL blog]

I have been working on a SQL DW database and revisiting an existing SQL table partitioning scheme.  The main reasons we’re going to all this trouble is to (a) implement a full partition based optimisation process and (b) implement a partial database backup scheme. (I will blog about these later!)

The SQL DW database has grown significantly and some of the tables are large (2.1+ billion rows) so nearly everything you do with it takes time, needs to be considered and tested.  The data is not a candidate for archive and therefore the partition sliding window technique is not being considered.

The partitioning includes several major components of work (and can be linked below);

  1. partitioning large existing non-partitioned tables
  2. measuring performance impacts of partitioned aligned indexes
  3. measuring performance impacts of DML triggers for enforcing partitioned unique indexes
  4. rebuilding tables that are already partitioned (ie. apply a new partitioning scheme)
  5. implementing partial backups and restores (via leveraging partitions)
  6. implementing partition aware index optimisation procedures
  7. Calculating table partition sizes in advance

This first blog post deals with partitioning large existing non-partitioned tables. I will blog about the other steps later.

And so, lets get into the nitty gritty of the partitioning details!

Continue reading →

Mr. Fox SQL Blog…

Mr. Fox SQL (Rolf Tesmer)

So I’ve decided that its finally time I took a bite out of the magic apple and started doing some Blogging.

Starting a blog has been on my mind for some time (read years), and I never really got onto it mostly due to timing issues.

As a SQL Consultant I’ve read, accessed and joined in on many blogs over the years and its been (quite frankly) invaluable, so this is an opportunity to share some of my own interesting things that I have picked up over the past years (which is my part to do), and with some patience and luck (which is your part to do) I hope that you find them interesting too!

So this is essentially the first blog post of (hopefully) many!

So stick around and follow if interested – !

Lastly, my personal caveat, SQL is a large and complex product, so keep in mind nothing can be tested or investigated from every angle 100% of the time and so there may be inaccuracies or limitations.  Constructive feedback is appreciated and I can correct where applicable!

If you want to know a few things About Me then go here

All content on Mr. Fox SQL blog is subject to the Disclaimer found here

Rolf Tesmer (aka Mr. Fox SQL)