AUTO_CLOSE and performance issue in a .NET application

  • Article

One of the interesting scenario I worked recently.

.NET application which has multiple tabs showing employee details shows poor performance when run for the first time during anytime of the day but when we connect to SQL Server Management Studio and run any ALTER COMMAND, then switch back to application, performance is as expected.

This .NET application is used by several customers and within each customer environment, the number of users accessing the application is < 10. These 10 users also access the application infrequently.

As a good programmer, the developer who wrote the application, opened a connection, queried the database and closed the connection immediately.

There are multiple tabs in the application to select the employee name like Tab 1 for employee names starting with A-C, Tab 2 for employee names starting with D-F.

The target database against which the queries are executed has AUTO_CLOSE turned ON. As per https://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx the moment last user exits, database is shutdown (auto closed) within 300 ms.

So when a user launches the application, opens a tab, connection is made to SQL Server and it is closed immediately after reading the data. If this user is the only user and he takes more than 300 ms to switch to next tab, database need to re-opened and that's how performance is down.

So what happens when we run any ALTER command which makes performance look good? It is because a connection is currently open in the management studio so the database cannot be shutdown.

There is no message printed in SQL Server Errorlog when the database is shutdown. Simple way to track whether the database is already shutdown is to use the T-SQL command:

SELECT name, collation_name FROM sys.databases

Collation Name for a database will be NULL if it is already shutdown. So from the query given above, a database with NULL in the collation name is currently auto closed.

Solution in this case could be turn off AUTO_CLOSE using the command given below or any other programmic logic to keep connection active (with sleeping state) till the user exits application:

ALTER DATABASE [Database] SET AUTO_CLOSE OFF WITH NO_WAIT

Sakthivel Chidambaram, SQL Server Support