With the February release of the SQL Server Management Studio (SSMS) Preview Microsoft introduced a nice new tool inside SSMS, the Plan Comparison Tool.
Like the name suggests, the tool makes it possible for you to compare different plans inside SSMS, but it doesn’t stop there. Microsoft also managed to squeeze in some optimizations for viewing Execution Plans through SSMS that makes navigating big Execution Plans a lot easier!
Let’s take a look at the new plan comparison options through SSMS, after that we’ll go through the various optimizations inside the Execution Plan viewer.
You can download the latest SSMS Preview version using this link: https://msdn.microsoft.com/en-us/library/mt238290.aspx if you are interested in reproducing the examples in this article.
Comparing Execution Plans inside SSMS
To prepare for the examples I saved two different Execution Plans for the same query. The query I used is a query I ran against the AdventureWorks database and you can find it below.
SELECT DISTINCT
pp.LastName,
pp.FirstName
FROM Person.Person pp
JOIN HumanResources.Employee e
ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN
(
SELECT SalesPersonID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN
(
SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID IN
(
SELECT ProductID
FROM Production.Product p
WHERE ProductNumber =
(
SELECT TOP 1 ProductNumber from Production.Product ORDER BY NEWID()
)
)
)
)
–OPTION (MERGE JOIN)
Notice the commented –OPTION parameter. I ran the query once using the MERGE JOIN option commented, and once uncommented to make sure I got a different Execution Plan. The Execution Plans of these queries I saved inside a folder.
With both plans stored on my machine, let’s start by comparing them.
The first step is to open one of the stored Execution Plans, which one doesn’t matter but since I like to work in chronological order I started with ExecutionPlan01. When you double-click on the Execution Plan file SSMS will open and it shows you the Execution Plan.
To compare another Execution Plan against the plan we just opened you only need to right-click anywhere in the plan and select the option “Compare Showplan” as shown in the image below.
This will open the file explorer where you can navigate to the Execution Plan file you want to compare. After selecting a file, SSMS will open a new tab with both Execution Plans and adds some extra panels that directly show off some of the new plan comparison options.
Let’s go through some plan comparison options using the image above.
One of the first things you might notice are the highlighted areas in both Execution Plans. The Plan Comparison tool highlights operations inside the Execution Plans that perform identical actions. In this figure above we can see both Execution Plans performed a Clustered Index Scan against the PK_Employee_BusinessEntityID index. Because this operation is identical in both plans (even though they have a different cost and return a different amount of rows), the Plan Comparison tool highlighted these actions.
In the window below both Execution Plans (Showplan Compare Options) you can easily identify all the highlighted operations including what color and border style has been chosen to highlight them. Clicking on of the highlighted operations inside the Showplan Compare Options window will jump both Execution Plans to the highlighted operation you selected. This makes it a lot easier when you want to compare the different costs or results of the operations between Execution Plans. For instance, before the Plan Comparison tool we would have to scroll all through the Execution Plans ourselves and make note of identical operations and their properties.
Speaking of properties, when you select a highlighted operation in one of the Execution Plans you are comparing the Properties window to the right side will automatically show the properties of the operation for both Execution Plans. In the image below I selected another similar operation (Index Scan on IX_SalesOrderHeader_SalesPersonID), notice how the properties window shows the properties of this operation for both plans.
Also note the arrow icons, these indicate the different properties values between both the Execution Plans. For instance, in the top plan the Actual Number of Rows was 0, while in the bottom plan the Actual Number of Rows was 31465. If you are interested in the global properties of both plans you can choose the SELECT operator to get a quick view of the differences between the plans for things like Degree of Parallelism or memory grant information.
Using the arrow icons you can quickly identify the differences in the similar operations between Execution Plans, again saving lots of time!
I think the Plan Comparison tool is a great addition to the SSMS product. While it’s true there already are some great commercial and free tools available to compare Execution Plans, integrating this feature directly into SSMS makes plan comparison easier available for everyone without the need to install additional software.
Now that we took a brief look at the new Plan Comparison tool, let’s look at the optimizations of the Execution Plan viewer in general!
Execution Plan viewer optimizations
Next to the new Plan Comparison tool, lots of optimizations to the Execution Plan viewer have been pushed in the new SSMS Preview builds to make your live analyzing Execution Plans easier.
Navigation improvements
Navigating big Execution Plans could really be pain because you had to use to scroll bars to move through the plan (or the “minimap” thing at the bottom) and use the right-click menu to zoom. Thankfully that is a thing of the past now! Not only can you use mouse dragging to move through the Execution Plan, holding the CTRL button and using the mouse wheel will zoom in and out!
Different plan layout
Another slightly annoying thing of big Execution Plans are the distances the operators are of each other when many operators are involved at the right side of the plan. To give you a visual example, take a look at the Execution Plan of the query at the start of this article inside the SSMS of SQL Server 2014.
Notice how the operators at the bottom left have longer vertical lines running to them? The more operators on the right side mean longer vertical lines to operators on the left side.
Now look at the same plan in SSMS Preview.
It’s still the same Execution Plan as in SSMS 2014 but the layout of the operators is more “compressed” for easier navigation.
Plan loading performance
Along with the improvements in layout and navigation mentioned above, opening big Execution Plans in SSMS Preview has also been improved. The plan below, that has many operators, took around 5 seconds to load inside SSMS 2014 and only 2 in the SSMS Preview.
While this doesn’t sound like much, I can imagine seeing quicker loading time on even more complex Execution Plans.
Summary
It seems that SQL Server Management Studio is getting a lot of love lately with the monthly preview builds and so far I am impressed with the great new features and optimizations! With the release of SQL Server 2016 SSMS will no longer be included in the setup which means we will see more improvements to SSMS faster than ever before since we no longer have to wait for the next SQL Server release!
