SQL Server – How to compare execution plans


One of the challenges for DBAs and Developers was to compare execution plans effectively. In the latest SSMS released by MS we have an option of comparing execution plans in a better readable format. This short article will explain that.

Step 1: Download the latest SSMS from MS website or If you have installed SQL server 2016 then this feature will be available.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Step 2: Install the downloaded package

Step 3: Execute a query and save the execution plan somewhere.

Step 4: Execute another query and wait till the plan is displayed.

Step 5: Right click on the displayed plan and choose the following option highlighted.

This will ask for the saved execution plan. Choose the saved plan.

Step 6: Click on open
The actions opens 2 windows which shows the difference between the 2 execution plans as depicted below. This is a very handy feature when we need to compare the execution plans for a SP or query pre and post change to gauge the effect in terms of performance.

 

 

 

I hope this article was useful. Have a good day ahead.
Bodhisatya

Advertisements

Tagged: , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: