[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);
- partitioning large existing non-partitioned tables
- measuring performance impacts of partitioned aligned indexes
- measuring performance impacts of DML triggers for enforcing partitioned unique indexes
- rebuilding tables that are already partitioned (ie. apply a new partitioning scheme)
- implementing partial backups and restores (via leveraging partitions)
- implementing partition aware index optimisation procedures
- 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!
Mr. Fox SQL 