Database Auto Close Enabled

Setting the AUTO_CLOSE option to ON will cause SQL Server to close the database to free up resources, such as the buffer and procedure caches.

New connections to the database will require SQL Server to open the database, which not only takes time and slows down your query, but the query will not benefit from pages being in memory or the plan cache.

Suggested Action

šŸ’” Aireforge Advisor can automatically generate a script for this.

Set AUTO_CLOSE to OFF for the databases, unless the database is used infrequently AND the instance is suffering from severe resource contention.

How to Disable Auto Close for a Database

USE [master]
GO
ALTER DATABASE [WhippetWorks] SET AUTO_CLOSE OFF WITH NO_WAIT;
GO

Further Reading

ALTER DATABASE SET Options (Transact-SQL) | Microsoft Docs