I think you need to distinguish between index statistics and column statistics.
Index statistics are those statistics for a specific index, that get generated regardless of the AUTO_UPDATE_STATISTICS setting. These statistics are automatically updated when you perform an index rebuild.
Column statistics are statistics for columns that are not the leading column in any indexes for a table. They are usually generated automatically by SQL Server, when a WHERE predicate references that column, if the AUTO_UDPATE_STATISTICS setting is active. They can also be generated manually. However, they are not automatically updated when you rebuild indexes. They are only updated when a certain threshold of changes have been made to the table (SQL2000) or column (SQL2005), and for large tables, it is likely that only a sample of the rows are used to update the statistics.
So should you leave the AUTO_CREATE_STATISTICS setting on? Yes, if you want SQL Server to create column statistics automatically.
What if you turned off the AUTO_UPDATE_STATISTICS setting?
- you'll need to manually update column statistics.
- your index and column statistics has to be representative of the table data until the next update. If there is a large number of modifications to the table, your statistics may become inaccurate, resulting in sub-optimal execution plans.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.