Compare Plans with SSMS 2016+

Microsoft has been making great strides with their database management platform lately. One really great improvement in their management tool, SQL Server Management Studio, is the “Compare Plans” feature. This allows visually comparing plans to see how they vary.

As an example, take the following two queries, which SQL Server auto-parameterizes. Since both parameter values vary widely, SQL Server creates two different plans for what are, at first glance, the same query. Using the “Compare Plans” feature allows us to pretty quickly understand the details about why SQL Server has different plans for them:

The actual execution plans for the two queries are:

compare plans - #1
compare plans - #2

The dbo.OnDiskNumbers table is a typical “numbers” table:

The table has 10,000,000 rows. I’ve create a non-clustered columnstore index on the table, which I’ll talk about in a future post. I’ve included it here because it provides a succinct difference in the two plans.

To compare the plans visually, side-by-side, you need to save the first plan by right-clicking on the plan window, clicking “Save Execution Plan As…”, and specifying a filename. Next, right-click on the plan window, and choose “Compare Showplan”:

At this point, select the saved Execution Plan file you saved above, which will result in the Execution Plan window switching to the comparison view, which includes both graphical execution plans:

If you have the “Properties” window open, you can compare details about the plans, such as Actual Number of Rows, Memory Grants, etc:

These details allow us to notice the vastly different number of rows that are returned by each query, and shows how the access plans for each differ in a way that quickly pinpoints the differences.