When you run a query twice, and get dramatically different run times, your first step can be to try to identify the differences in execution plans. For many that means running the two different queries (before & after) and splitting you screen in order to visually compare the plans. Did you know SQL Server Management Studio gives you the option to compare to different execution plans? It makes it easy not only to visualize the differences, but it also shows you detail properties that allow you to dive into the numbers. This functionality was introduced with SQL Server 2016, and is also part of the functionality of the Query Store GUI shown below.
The SQL Server Management Studio comparison process allows you to compare any two .sqlplan files including those from old SQL Server versions. In the comparison windows you will see the top plan is the first plan you open or have run and the lower will be the second plan chosen for comparison. As you can imagine this a great tool to use prior to migrations, and as an added bonus, you have the ability to compare the plans offline so no need to connect to a SQL Instance. This information can be very useful and should be added to your testing check lists.
When comparing the plans, Management Studio highlights similar query operators by shading them with the same color. The best place to start looking for differences is in the properties pane, which you can see on the right—where there’s a not equal sign (≠) you can easily see what is different in costing these two plans.
Let’s see it in action.
To compare execution plans you need to first save one of the plans you wish to compare. To save the plan just right click in the execution plan area and choosing Save Execution Plan as shown below in purple.
Once you have a plan saved, go ahead and run the plan you wish to compare to. After execution keep that window open, then right click and choose Compare Showplan and pick your saved plan. Below example is from docs.microsoft.com. As you hover over each node you can dive into the properties and numbers on the lefthand side by default.
The properties allow you to clearly see the cost and actual row count difference between each node in each plan.
It is important to examine each of the places where the plans are different. I’ve run into situations where two plans had the exact same plan shape, and only minor differences in memory grants, but the CPU consumption by each plan was vastly different. This is the kind of scenario where the properties comparison is invaluable. The old approach was to parse the XML of each plan to get the different memory grants.
This is just a quick reminder that SSMS gives us this functionality built in. I encourage you to take it for a test drive.