Disconnecting SQL Server connections after query execution
By: Rahul Mehta | Comments (9) | Related: More > SQL Server Configurations
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.
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.
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.
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.
- 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.
About the author
View all my tips