Category Archives: SSMS

Try and try again – SSMS

SSMS April 16, 2017 Leave a comment

Came across this very interesting article which talks about connection resiliency logic in the SQL Server management studio. What this basically does is try to re-connect in case of a time out after sleeping for 10 seconds. The default value for retry is 1 and default interval is 10 seconds. This article from Arvind Shyamsundar explains how we reset the value and how in some scenarios this might not have a good impact.

https://blogs.msdn.microsoft.com/sqlcat/2017/04/06/try-and-try-again-not-always-a-good-idea-at-least-not-for-ssms/

White Paper on connection resiliency

https://msdn.microsoft.com/en-us/library/dn632678.aspx

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

 

 

Advertisement

SQL Server – How to compare execution plans

SQL Server 2012, SQL Server 2014, SQL Server 2016, SSMS April 11, 2017 Leave a comment

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

%d bloggers like this: