Partitioning is one of the most powerful tools at your disposal when managing large volumes of data and improving the performance of queries that would otherwise scan and filter lots and lots of data.
However, it can be tricky to determine why it isn’t helping when you thought it should.
Typically partitioning improves query performance by ensuring only the partition(s) needed to answer the business user’s query will be scanned rather than the entire table.
But how can you tell if you got partition pruning in an execution plan? Or worse yet, how do you determine why you didn’t get partitioning pruning when you were expecting it.
In the video below, I share the steps I use to determine if partition pruning has occurred and what to look at and correct if you don’t automatically get the partition pruning you were expecting.
This blog post is part of a series on SQL Tuning. I shared some simple steps to help you tune a SQL Statement using the wrong Join Type in part one. While part two deals with how to tackle a problem where the optimizer picks the wrong index, and part three shares tips on how to fix statements where the Optimizer chooses a nested loop join instead of a hash join.