This database option is designed for use with the Desktop version of SQL Server 7.0 and 2000, not for the server versions. Because of this, it should not be turned on (which it is not, by default). What this option does is to close the database when the last database user disconnects from the database. When a connection requests access to the database after it has been closed, then the database has to be reopened, which takes time and overhead.
The problem with this is that if the database is accessed frequently, which is the most likely case, then the database may close and reopened often, which puts a large performance drag on SQL Server and the applications or users making the connection.
As part of your audit, if you find this option turned on, and you are not using the desktop version of SQL Server, then you will need to research why it was turned on. If you can’t find the reason, or if the reason is poor, turn this option off.
When auto_create_statistics is turned on (which it is by default), statistics are automatically created on all columns used in the WHERE clause of a query. This occurs when a query is optimized by the Query Optimizer for the first time, assuming the column doesn’t already have statistics created for it. The addition of column statistics can greatly aid the Query Optimizer so that it can create an optimum execution plan for the query.
If this option is turned off, then missing column statistics are not automatically created, when can mean that the Query Optimizer may not be able to produce the optimum execution plan for the query, and the query’s performance may suffer. You can still manually create column statistics if you like, even when this option is turned off.
There is really no down-side to using this option. The very first time that column statistics are created, there will be a short delay as they are created before the query runs for the first time, causing the query to potentially take a little longer to run. But once the column statistics have been created, each time the same query runs, it should now run more efficiently than if the statistics did not exist in the first place.
As part of your audit, if you find this option turned off, you will need to research why it was turned off. If you can’t find the reason, or if the reason is poor, turn this option on.