I am considering partitioning a table.

I am trying to think in terms of how we query that table so that I achieve partition elimination.

Suppose I have a table that has a partition by month, what would I do to cause a partition elimination in my queries?

how could I test whether or not the query is causing partition elimination?

I heard I could even use a calculate field, as long as it is persisted.


For partition elimination to come into play, you would need to use the partitioned column in your join or predicate clauses which would allow SQL Server to narrow down the search using the partition metadata available. To check if partition elimination is occurring or not, look for PtnId or RangePartitionNew in the query plan.

See this post for more information. If you want to look at the XML plan, then look for RunTimePartitionSummary which provides the partition access summary details. However, this would need to be retrieved from the actual query plan.

Another option would be to partition align your indexes so that they can help with searches on additional columns. An example of that is available in this post.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.