[read this post on Mr. Fox SQL blog]

Continuing on with my Partitioning post series, this is part 4.

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 blog post deals with rebuilding tables that are already partitioned (ie. apply a new partitioning scheme).

I will blog about the other steps later.

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

Continue reading →

[read this post on Mr. Fox SQL blog]

Continuing on with my Partitioning post series, this is part 3.

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 blog post deals with partition aligning non-clustered indexes (unique and non-unique) and then measuring performance impacts of DML triggers for enforcing partitioned unique indexes.

I will blog about the other steps later.

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

Continue reading →

[read this post on Mr. Fox SQL blog]

Continuing on with my Partitioning post series, this is part 2.

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 blog post deals with deciding whether to align non-clustered indexes to the table (or not) via Measuring Performance Impacts of Partitioned Aligned Indexes.

I will blog about the other steps later.

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

Continue reading →

[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 →