Hello Friends,

During performance troubleshooting, It was not easy to compare the execution plan for the queries. We generally save the execution plans and then compare them by opening in different SQL Server Management Studio windows but it was not easy to compare the various things about two operators from the plan. In new SSMS Microsoft introduced a new compare showplan option. This is really helpful in various performance comparison scenarios and easy to use.

Step 1: Save your execution plans as .sqlplan on your machine.

Step 2: Open one execution plan in SSMS and right click anywhere in that plan window. Click on Compare Showplan.

Compare Showplan_1

Step 3: Step 2 will open up a popup window to select the another plan that you wants to compare. Select the new plan that you wants to compare.

Compare Showplan_2

Step 4: Now you will see a new windows that will contain both execution plan and details about the selected operators in both plans to compare. I’ll show you the screen in two parts:

Compare Showplan_3

Compare Showplan_4

From above first image you can easily see the same or different operators in the execution plans. For looking into more details about the operators, you can compare every property of the operator in second image (this will come to the right hand side on the screen). You can also change the operators from the drop down on the top of each properties section.

In my case bottom plan is using parallel plan and in properties section the only difference between clustered index scan operator is the CPU cost i.e. almost half. This tool helps me lot for performance troubleshooting scenarios and saved a lot of time as well.



Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook