What does this auto option means in SQL Server database.
AUTO_CLOSE { ON | OFF }
AUTO_CREATE_STATISTICS { ON | OFF }
AUTO_SHRINK { ON | OFF }
AUTO_UPDATE_STATISTICS { ON | OFF }
AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
The auto options affect actions that SQL Server might take automatically. All these options are Boolean options, with a value of True or False.
AUTO_CLOSE((Default False)) When this option is set to True, the database is closed and shut down cleanly when the last user of the database exits, thereby freeing any resources.
All file handles are closed, and all in-memory structures are removed so that the database isn’t using any memory. When a user tries to use the database again, it reopens. If the database was shut down cleanly, the database isn’t initialized (reopened) until a user tries to use the database the next time SQL Server is restarted.
By default it is set to False in Enterprise edition but in Sql Server Express edition it is set to True.
AUTO_SHRINK(Default False) When this option is set to True, all of a database’s files are candidates for periodic shrinking. Both data files and log files can be automatically shrunk by SQL Server. The only way to free space in the log files so that they can be shrunk is to back up the transaction log or set the recovery model to SIMPLE. The log files shrink at the point that the log is backed up or truncated. This option is never recommended.
AUTO_CREATE_STATISTICS((Default True)) When this option is set to True (the default), the SQL Server Query Optimizer creates statistics on columns referenced in a query’s WHERE, ON, GROUP BY, or DISTINCT clauses. Adding statistics improves query performance because the SQL Server Query Optimizer can better determine how to evaluate a query.
AUTO_UPDATE_STATISTICS((Default True)) When this option is set to True (the default), existing statistics are updated if the data in the tables has changed. SQL Server keeps a counter (colmodctrs) of the modifications made to a table and uses it to determine
when statistics are outdated.You can not see the metadata of counter without using DAC account. When this option is set to False, existing statistics aren't automatically updated.
(They can be updated manually.)
Statistics are checked before query compilation or before executing a cached query plan. Statistics are considered out-of-date when:
There was a data change on an empty table.
The number of rows in the table was 500 or less at the time of statistics creation and the column modification counter of the leading column of the statistics object has changed by more than 500 since then.
The table had more than 500 rows when the statistics were gathered, and the column modification counter of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered.
A table in TempDB with less than 6 rows had at least 6 row modifications.
AUTO_UPDATE_STATISTICS_ASYNC((Default False)):Introduced in SQL Server 2005. This allows the statistics update operation to be performed on a background thread in a different transaction context. When this option is enabled, the Query Optimizer will not wait for the update of statistics, but will run the query first and update the outdated statistics afterwards. Your query will execute with the current statistics and a background process will start to update the statistics in a separate thread. It should be noted that the Query Optimizer may choose a sub-optimal query plan if statistics are outdated when the query compiles. When this background operation is complete, the new query requests will use the new updated statistics.
References: Sql Server Internals
AUTO_CLOSE { ON | OFF }
AUTO_CREATE_STATISTICS { ON | OFF }
AUTO_SHRINK { ON | OFF }
AUTO_UPDATE_STATISTICS { ON | OFF }
AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
The auto options affect actions that SQL Server might take automatically. All these options are Boolean options, with a value of True or False.
AUTO_CLOSE((Default False)) When this option is set to True, the database is closed and shut down cleanly when the last user of the database exits, thereby freeing any resources.
All file handles are closed, and all in-memory structures are removed so that the database isn’t using any memory. When a user tries to use the database again, it reopens. If the database was shut down cleanly, the database isn’t initialized (reopened) until a user tries to use the database the next time SQL Server is restarted.
By default it is set to False in Enterprise edition but in Sql Server Express edition it is set to True.
AUTO_SHRINK(Default False) When this option is set to True, all of a database’s files are candidates for periodic shrinking. Both data files and log files can be automatically shrunk by SQL Server. The only way to free space in the log files so that they can be shrunk is to back up the transaction log or set the recovery model to SIMPLE. The log files shrink at the point that the log is backed up or truncated. This option is never recommended.
AUTO_CREATE_STATISTICS((Default True)) When this option is set to True (the default), the SQL Server Query Optimizer creates statistics on columns referenced in a query’s WHERE, ON, GROUP BY, or DISTINCT clauses. Adding statistics improves query performance because the SQL Server Query Optimizer can better determine how to evaluate a query.
AUTO_UPDATE_STATISTICS((Default True)) When this option is set to True (the default), existing statistics are updated if the data in the tables has changed. SQL Server keeps a counter (colmodctrs) of the modifications made to a table and uses it to determine
when statistics are outdated.You can not see the metadata of counter without using DAC account. When this option is set to False, existing statistics aren't automatically updated.
(They can be updated manually.)
Statistics are checked before query compilation or before executing a cached query plan. Statistics are considered out-of-date when:
There was a data change on an empty table.
The number of rows in the table was 500 or less at the time of statistics creation and the column modification counter of the leading column of the statistics object has changed by more than 500 since then.
The table had more than 500 rows when the statistics were gathered, and the column modification counter of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered.
A table in TempDB with less than 6 rows had at least 6 row modifications.
AUTO_UPDATE_STATISTICS_ASYNC((Default False)):Introduced in SQL Server 2005. This allows the statistics update operation to be performed on a background thread in a different transaction context. When this option is enabled, the Query Optimizer will not wait for the update of statistics, but will run the query first and update the outdated statistics afterwards. Your query will execute with the current statistics and a background process will start to update the statistics in a separate thread. It should be noted that the Query Optimizer may choose a sub-optimal query plan if statistics are outdated when the query compiles. When this background operation is complete, the new query requests will use the new updated statistics.
Thanks For Reading this Blog!!
References: Sql Server Internals
Excellent...
ReplyDeleteThank you :)
Delete