SQL Server – Compare Showplan

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


About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.