Disconnecting SQL Server connections after query execution

By: Rahul Mehta | Updated: 2016-12-06 | Comments (9) | Related: > 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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author

MSSQLTips author Rahul Mehta

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2016-12-06

Comments For This Article

Tuesday, June 1, 2021 - 7:56:13 AM - Raman	Back To Top (88775)
Can you please share pros and cons to enable the settings "Disconnect after the query executes"

Thursday, August 9, 2018 - 11:29:53 AM - Pooja Bharti

Back To Top (77104)

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 2, 2017 - 11:16:17 PM - Ben

Back To Top (45084)

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 (45017)

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 (44973)

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 (44961)

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 (44957)

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 (44950)

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 7, 2016 - 11:18:57 AM - Ray Herring

Back To Top (44916)

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.