Comparing Plans via Query Store

A small but nice little feature I have been using recently can be found within Query Store.

Let’s say you have 2 Plan IDs for a query, naturally you want to view the execution plan for the different plans. In the past I did it a manual way, by that I mean by individually clicking on the Plan ID to see the plan then moving on to the next one.

It is much easier to explain with some images.

PlanIDS

The Y axis is actually Duration (m/s) so Plan ID 2 is as follows:

plan1

Plan 1 is as follows:

plan2

While this is a trivial example it would be nice to have a side by side view of the plans. Well you can!

In the top menu bar you will need to click on the button highlighted below via the red arrow.

arrow

Well OK, it would help to select the plans that are of interest.

ssmserr

Once you “Shift + click” you will have the comparison window. First for the actual plans and also for a specific operator that you are interested in.

sidebyside

Nice to see a classic hash vs nested loop join, below shows the SELECT operator differences.

selectop

If you want to compare a different area within the execution plan (like the JOIN) you can by simply clicking on the operator, below shows both the hash and nested loop join being highlighted.

joins

Scroll across your screen to dig into the details.

loopvshash

Like I said, not a complicated blog post but I feel it is quite handy to know about!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s