SQL Server

Experiences of Submitting a PASS Session

Mr. Fox SQL4 Comments

[read this post on Mr. Fox SQL blog]

The PASS speakers were announced a quite a number of weeks ago now for PASS 2015 in October – and I felt pretty damn honored to be one of the 160 or so selected to speak at the conference (…and as a quick aside I also note a reasonably large contingent of Aussie & Kiwi presenters too!).

It will be my 2nd conference, but first time speaking at it.

It actually came as a (welcome!) surprise having attended my first PASS in 2014 (actually on recommendation of fellow Aussie Rob Farley) and then to go-on and submit a session for PASS 2015 (on suggestion of yet another fellow Aussie Victor Isakov).

Both guys are also presenting at PASS 2015, checkout their sessions.

So what was my experience of submitting a PASS session, and how did the process unfold? (read on!)

Continue reading →

Implementing Partial Backups and Restores (via Leveraging Partitions)

Mr. Fox SQL10 Comments

[read this post on Mr. Fox SQL blog]

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

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 implementing partition aware partial backup and restore procedures.

I will blog about the other steps later.

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

Continue reading →

Pie Charts Aren’t THAT Evil!

Mr. Fox SQLLeave a comment

[read this post on Mr. Fox SQL blog]

Its time to take a well deserved 1/2 time break in my 8 part post series on SQL Partitioning and so I have decided to take a slight “light-hearted” tangent and talk about visualisations, or more specifically Pie Chart visualisations.

In all seriousness, this actually came up as I overheard a conversation at a client site debating the usage of this very visualisation.

Now – If you believe everything you read on the Internet about Pie Charts you may begin to think they are the proverbial trouble-maker of the BI World, but I believe that they deserve a chance to prove themselves!

So to prop up my rickety case, this post will explore Pie Chart Visualization Best Practices and then compare the default Pie Chart visualization from 10x industry leading BI/Reporting Tools to see how they stack up against this Best Practice list.

And so, lets get into the nitty gritty of creating and eating pie charts!

Continue reading →

Rebuilding Existing Partitioned Tables to a New Partition Scheme

Mr. Fox SQL9 Comments

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

Performance Impacts of Partitioning DML Triggers

Mr. Fox SQL9 Comments

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

Performance Impacts of Partitioned Aligned Indexes

Mr. Fox SQL9 Comments

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

Rebuild a Standard Table to a Partitioned Table

Mr. Fox SQL16 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 →