This is a short look at a technique that I discovered recently in Management Studio (SSMS). The technique is to compare two graphical execution plans in the tool to understand what they are doing and how two different queries might affect a particular system, both with the image and with the properties' data behind the graphical plan.

This is actually something that was added in SSMS v16, but I hadn't upgraded until relatively recently, in the v18s. I tend to use older versions of SQL Server, so I have stuck with the version of SSMS that came with them. However, with a little push from multiple developers, we have started to try and keep up with SSMS a little more closely. Most people in my organization are now running at least v18.1, though I'm not sure many have gotten past 18.6. I think we aim to try and upgrade a couple times a year.

If you need to download SSMS, you can do so here.

Generating Execution Plans

There are a few ways to get an execution plan, but I'm going to show you a simple way to do this on your own system. First, open SSMS. Then you can use CTRL+M, the menu, or the right angle shaped icon with boxes to get the execution plan. You can see the menu item below:

I tend to look for the actual execution plan so that I can compare two plans based on the data we have in our QA database.

For this article, I am using AdventureWorks, which is freely available from Microsoft.

Once I click the button, I want to run a query. At the bottom of SSMS, where I see the Results and Messages, a third tab appears with the Execution Plan. If I click anywhere in the lower plan, I get a menu that lets me save the plan.

I can save this as any name I want. I'll choose ADW1.sqlplan as the name. The default extension for execution plans is .sqlplan.

Now I have one execution plan. I can save others if I wish. However, I'm going to now change my query and find a better one. We know SELECT * is bad. I'm not going to mess with the indexes, but instead just reduce the columns I return. Now I see a completely different plan.

I'll save this as ADW2.sqlplan.

Comparing Plans

I want to compare this one with the previous one. I could have a separate tab group, or two instances of SSMS or something else. However, I'll now right click in the new plan and look at the menu. I see this, which includes the "Compare showplan" option.

If I pick that, I get an Explorer box to pick a new plan. I'll pick ADW1.sqlplan. This opens up the plans and gives me both plans at the same time.

In the image above, I see the two plans, one of which is a little simpler than the other. I also see an analysis at the bottom, which may or may not have some different information for each query. I can see which query is which in the text above the icons, and then see how different thicknesses of arrows might be changing how the plan works.

On the right side of SSMS, I have both sets of properties.  As you can see in the image below, the right plan (without SELECT *) has a lower cost and the plan compilation ended early. A good enough plan works for this one.

Summary

This is a quick article to show you how to compare two plans. You can even do this if you open a showplan directly from File|Open File, and then right click to open a second one.

This doesn't mean I get better queries, or even that I pick a good query. It is will up to me to tune the queries appropriately, set indexes, and understand how execution plans work. That is something you need to know, and you might check out Grant Fritchey's SQL Server Execution Plans. This is, however, a tool to help you start to experiment with different code and see what the impact is on your query performance.