SSMS Tips and Tricks 6-1: Comparing query plans

One of the advantages of SSMS is that it can be used to analyze queries, not just to execute them.

There are two basic types of query plan: estimated execution plans, and actual execution plans.

For a typical query, I can obtain the estimated execution plan, by hitting Ctrl-L, choosing the option in the Query menu, or clicking on the toolbar icon:

Let’s do this for the following query:

We get the following plan output:

Now we might look at this plan and wonder if we exerted control over how the joins were performed, if we’d improve things. Perhaps we’d heard that merge joins were more efficient and thought SQL Server should have used those.

Now we can change the query like this:

Notice I’ve added the word MERGE between INNER and JOIN in this query. But how do we know what SQL Server thinks? We can get another estimated query plan but what we really want is to compare them.

When we obtain an estimated plan for multiple queries at once, SSMS shows us a comparison of the two, by showing us the proportion of the overall query for each part. (Perhaps we shouldn’t force that change 😊)