Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Disconnecting SQL Server connections after query execution


By:   |   Last Updated: 2016-12-06   |   Comments (8)   |   Related Tips: More > SQL Server Configurations

Problem

One factor to improve SQL Server performance is to utilize as little resources as possible. In SQL Server, when most queries are executed they maintain the connection to the database which utilizes resources. Imagine the resources utilized for systems that have thousands of users querying the system. The connections get established, but may not get disconnected.

Solution

One can restrict the SQL Server resource utilization by disconnecting queries post execution with a simple change which is implemented in SQL Server Management Studio.

To demonstrate how to do so, I have executed SQL Server queries on the AdventureWorks2016 database. The queries completed yet the connection to the database remains as shown in the below image.

Sample Connection to a SQL Server Database

To disconnect the connection after the query completes, from the menus go to Tools > Options > Query Execution > SQL Server > Advanced and select "Disconnect after the query executes". By checking this option, after the query executes the database connection will be disconnected.

Configuration in SQL Server Management Studio to disconnect queries after execution

After this point, any query executed will be disconnected and no further threads or connection will remain established. As shown below, one can see the message "Disconnected" in the bottom left once the query is executed.

Disconnected Connection shown in SQL Server Management Studio
Next Steps
  • Use the disconnect option to terminate all of the left over connections directly from SQL Server Management Studio.
  • This is an application wide change once enabled.
  • This was tested on SQL Server 2014 and 2016
  • For more on SQL Server 2016, read these other SQL Server 2016 Tips.


Last Updated: 2016-12-06


next webcast button


next tip button



About the author
MSSQLTips author Rahul Mehta Rahul Mehta is a Project Architect/Lead working at Tata Consultancy Services focusing on ECM.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, August 09, 2018 - 11:29:53 AM - Pooja Bharti Back To Top

I am not able to connect my sql server with netbean.

while establishing connection I am getting this error (Cannot establish a connection to jdbc:mysql:localhost:3306/mysql?zeroDateTimeBehaviour=convertToNull using connection)


Monday, January 02, 2017 - 11:16:17 PM - Ben Back To Top

 Thanks for taking the time to write this tip.

 

Here is what I don't understand. How is this supposed to improve performance?

 

By disconnecting from the server after the query executes, when I wish to run another query, I have to wait for a new connection to be established.

 

Maintaining connections is a good, performant thing...except on a production server with a LOT of activity.

 

Each database connection takes about 1meg of ram on the database server. So, you don't want to keep them just because you may want to use it in the next hour. However, if I am working in SSMS in an ad-hoc or develoopment mode, disconnecting from the database is not a performance improvement for the server or the client.

 

Keep writing...you're good at it.

 

Ben


Wednesday, December 21, 2016 - 5:35:55 AM - Igor Micev Back To Top

Hi Rahul,

Developers usually use one session for multiple quering, so this option is even not good to be set up and use. If set up, then for every new query a new connection has to be established and that's not better than re-using the old session. 

After some time a session not being used, sql server marks that session with a suspended status and it's not eating any cpu time. 

 


Wednesday, December 14, 2016 - 3:17:32 PM - Ray Herring Back To Top

Hi Rahul,

I understand how the SSMS option works and I am sure you do also.  However, I think some of the statements in your post might lead newbies to think they are setting a server wide option rather than an SSMS option. 

After mentioning thousands of user connections you say

"One can restrict the SQL Server resource utilization by disconnecting queries post execution with a simple change which is implemented in SQL Server Management Studio."

This is true but it is a very local and limited improvement.  I have many applications and services connected to my databases.  Some of them run more than 100 queries per minute.  I don't want them connecting and disconnecting from the server 100's of times per minute.  The connection set up and tear down is much more resource intensive than just maintaining the connection.  That is why most providers (e.g., SQLNCLI, ODBC, ADO.Net, ...) implement some form of connection pooling.

Thanks for taking the time to put together the post and thanks for the followup.

 

 


Tuesday, December 13, 2016 - 1:00:41 PM - Rahul Mehta Back To Top

 Hi Ray,

I think you have mis-interpreted the meaning of "application wide setting". By application wide, it means "SSMS" as an application, and not client applications that consume data from SQL Server. The entire context of the query execution and disconnecting the connection is limited to the scope of SSMS. Nowhere in the tip it says that application queries will be disconnected.

I think if the tip would have been titled "Disconnecting SQL Server Connections after Query Execution from SSMS", it would have been more clearer.

 

Regards,

Rahul

 


Tuesday, December 13, 2016 - 10:43:37 AM - Ray Herring Back To Top

 You are correct Gordon.  That is what I meant.

The check box "Disconnect after the query executes" in the SSMS dialog <Tools><Options><Query Execution><SQL Server><Advanced> only applies to SSMS connections opened under the particular profile.

It is a client side setting and does not apply to the server.  So while it will close "my" connections it will not close the myriad connections from the various applications, ETL, users, etc.  I prefer to leave my SSMS connections open while I am working so I don't have to continually reconnect.

Connection options are generally controlled and set on a per session, per connection basis.


Monday, December 12, 2016 - 10:12:18 AM - Gordon Feeney Back To Top

 Hi Ray, I was interested in your reply to Rahul's post:

 

'I am pretty sure this does not accomplish exactly what you think.  The setting you have changed is specific to your SSMS application and does not affect any other users or applications.'

 

By this do youi mean that only queries executed within SSMS are disconnected rather than queries executed by external sources?

 

Gordon.


Wednesday, December 07, 2016 - 11:18:57 AM - Ray Herring Back To Top

 Hi Rahul,

I am pretty sure this does not accomplish exactly what you think.  The setting you have changed is specific to your SSMS application and does not affect any other users or applications.

 


Learn more about SQL Server tools