AUTO_CREATE_STATISTICS NOT USEFUL FOR CREATING STASTICS ON INDEXES

AUTO_CREATE_STATISTICS option does not determine whether statistics get created for indexes.
This option also does not generate filtered statistics.It applies strictly to single-column statistics for the full table. That is why even though AUTO_CREATE_STATISTICS enable on database level. We need to again update statistics for the Indexes. If you observed below table i have create a table with 3 columns and I ran the 3 different queries with by using 3 different columns in WHERE condition. And statistics created automatically created because of AUTO_CREATE_STATISTICS option. But this option will not create statistics on Indexes that is why we will run SP_UPDATESTATS once we did REBUILD indexes.





Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server