Friday, 5 September 2008

AUTO_CLOSE database option and its impact on the performance

The AUTO_CLOSE database option is only one of the many options related to performance and availability of the database in SQL Server.

When AUTO_CLOSE is set to ON for a database, SQL Server closes all its files and releases the resources used for it shortly after the last connection is closed. This action will reduce the usage of memory, nevertheless, it is barely insignificant (12KB or 20KB). Furthermore, having this option turned on, the first connection will have to open the database again, as a result, it will experience a delay. I highly recommend having disabled this option all the time. Here is the code to turn this option off:
  
       ALTER DATABASE [UserDBInProduction] SET AUTO_CLOSE OFF WITH NO_WAIT

This option is turned off by default, but I have found many databases with this option turned on which also impacts on it performance. The other disadvantage of having enabled it is that when the last established connection to the database is closed, its files are accessible to be manipulated directly via Windows by some user, which means that someone is completely able to delete them while the database engine is running. So, we need to work with lots of cautiousness when it comes to changing not only this database option but also others.

That is all for now, let me know any question you may have. Thanks for reading again.