Comparing execution plans with SSMS
November 3, 2015 Leave a comment
In SQL Server 2016 (now available as CTP 3.0), a new feature is shipped: Execution Plan Comparison Tool. This new (and very cool) feature allows you to compare 2 execution plans within SQL Server Management Studio (SSMS). And according to Amit Banerjee (Blog | @banerjeeamit), this feature is also available in the “SSMS – September 2015” release (more info here). Let’s take a look at this new feature.
To generate an execution plan that we can compare later on, let’s create a table with some data first:
USE Sandbox GO CREATE TABLE dbo.T1 (ID INT IDENTITY(1,1), VAL VARCHAR(10)) GO INSERT INTO T1 (VAL) VALUES ('X') GO 10
To generate some execution plans, run the queries below with the “actual execution plan” on, and safe these plans to disk:
SELECT * FROM T1 AS T1 INNER JOIN T1 AS T2 ON T1.ID = T2.ID
SELECT * FROM T1 AS T1 INNER JOIN T1 AS T2 ON T1.ID = T2.ID INNER JOIN T1 AS T3 ON T1.ID = T3.ID
Comparing execution plans
To compare execution plans in, you need to open the first execution plan from SSMS. In the plan-window, right-click and click on “Compare Showplan”:
Now a pop-up window will open, that asks you what plan you want to use to compare it with. Now open “Test2.sqlplan”. The compare window opens, and you can compare plans:
As you can see, parts of the plan that are the same in both execution plans, are colored the same. These colors are randomly chosen, and can be different every time you compare 2 plans. This makes it easy to determine where both plans are equal or differ.
If you click on one of the highlighted parts in the execution plan, the other plan will center its view on that part of the plan (unfortunately it’s a bit hard to see that with these small execution plans). If you click on a highlighted part, you can see that there is a blue rectangle drawn around the object:
Another really cool thing is the properties windows. If you don’t have that open by default, right-click on the first object in the execution plan (the SELECT part), and click on “Properties”. This shows 2 property-windows, in which you can compare the memory grant for both plans for example:
At this moment, you need to save both execution plans to disk in order to compare them. If you try to compare an execution plan of a query you just ran without saving it, it throws an exception:
But in my opinion this is just a minor issue, and I think this will be fixed in one of the next releases.
Comparing execution plans is definitely something I’ve been missing for years. Especially when you have 2 really big plans, this can really help you speed up the analysis and debug process.
Even though I’m pretty used to using SQL Sentry Plan Explorer to open execution plans, that doesn’t give me the option to compare plans (yet). At least not in the free version of the tool, that I always recommend to colleagues and friends to use.
This is another one of the really cool features in the overhauled SSMS, and I think the SQL Server team is rocking this new release.