sqlskills-logo-2015-white.png

Partitions Accessed and Partition Range in the Query Execution Plan

Let’s say you are querying a partitioned table and you would like to see which partitions were accessed by looking at the graphical execution plan:

SNAGHTML59cf4d6

“Actual Partition Count” shows a value of 1 and “Actual Partitions Accessed” shows a value of 50.  The “Actual Partitions Accessed” property name could cause confusion though, since what you’re actually looking at is the partition numbers accessed (not the count of partitions accessed). 

I prefer the XML naming convention instead:


  <PartitionsAccessed PartitionCount="1">
    <PartitionRange Start="50" End="50" />
  </PartitionsAccessed>

The name mapping is as follows from Graphical-to-XML Plan formats:

“Actual Partition Count” = PartitionsAccessed element & PartitionCount attribute

“Actual Partitions Accessed” = PartitionRange

If I modify the query to access two partitions, I see the following (graphical and XML plan output):

image

<PartitionsAccessed PartitionCount="2">
               <PartitionRange Start="1" End="1" />
               <PartitionRange Start="50" End="50" />
</PartitionsAccessed>

And here is an example accessing all partitions in the table:

image
 

<PartitionsAccessed PartitionCount="63">
    <PartitionRange Start="1" End="63" />
  </PartitionsAccessed>

Once you realize the mapping, its no big deal to understand what’s going on, although I do see it causing confusion (hence this blog post).

2 thoughts on “Partitions Accessed and Partition Range in the Query Execution Plan

  1. Excellent, thanks for clearing that up, Joe…Just what I was looking for!
    @SixStringSQL

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.