This month my friend Michael J Swart(b/t) would like us to talk about the new 2016 version of SQL Server. The feature I want to talk about has been around for a bit. Not because it’s not part of SQL 2016 but because it’s part of SQL Server Management Studio. SSMS is no longer tied directly to the rest of SQL Server and the 2016 version has been available for a little while now.

One of the new/not so new features that I’ve been playing with off and on is the ability to compare two query plans. I’m still relatively new at performance tuning and I frequently like to compare the query plans from before and after any changes I make. Historically I’d bring up both plans next to each other and walk through them section by section. You can see how this method would be a bit of a pain, particularly for large plans or plans that have changed quite a bit. But SSMS 2016 can now help us out.

Lets say for example I’m working on the view [AdventureWorks2014].[HumanResources].[vEmployee]. I decide that the OPTION FAST will speed the overall performance up.

First I run the view without any changes, displaying the execution plan.

Then I right click in the plan and select Save Execution Plan As… and save this plan.

Next I run the view with my changes, also displaying the execution plan.

You can see these plans are of radically different shapes. Even given it’s a relatively simple plan it might take a bit to go through the differences manually. So again I right click on the execution plan, but this time I select Compare Showplan.

This brings up a file search window. I use it to select the plan from the first query. And voilà!

You can see we have a fair amount of information here. In the two plans you can see that some elements are highlighted with various colors. Underneath them we have a key for those colors. And off to the right is a comparison of the properties of the currently selected element.

The important thing is those colors. Given how much the plan shape has changed those colors help us track sections of the plan that do the same thing. In this case, for example, the red dotted line is where we do an index seek on the EmailAddress table. But we can only really see it in the lower plan. But by clicking on that area it becomes the central area of both plans.

Since this is a single element we can also look off to the right and see all of the properties that are not the same.

Remember that this is a brand new (sort of) feature. It’s progressed quite a bit since it first came out and I expect it to continue to improve. Even as it stands however, it’s a very useful tool.

Filed under: Microsoft SQL Server, Query Plans, SQLServerPedia Syndication, SSMS, T-SQL Tuesday Tagged: microsoft sql server, query plans, SSMS, T-SQL Tuesday