Some updating commands could not be generated automatically
but keep reading to find out what those are and why it’s rare to require them.Auto-created statistics are incredibly small, and you only get one per column in a table.With default database settings, the SQL Server optimizer looks at how many changes have occurred for a given column statistic as part of query optimization.If it looks like a significant amount of rows in the column have changed, SQL Server updates the statistic, then optimizes the query.Because why optimize a query on bad data estimates?The thresholds for when auto-update statistics kicks in are a little complicated.See a graph of the adjusting threshold in this post from the SAP team.(I think we know which team really felt some pain and wanted this trace flag to exist, because the trace flag was announced on their blog! So if you’re using SQL Server 2016, you don’t need to decide.
Statistics will continue to be created on single columns when queries run as long as the “Auto Create Statistics” database property remains on.
I’ve been asked a lot of questions about updating statistics in SQL Server over the years. Here’s a rundown of all the practical questions that I tend to get about how to maintain these in SQL Server.
I don’t dig into the internals of statistics and optimization in this post. ⇒ Be a proactive: If you have millions of rows in some of your tables, you can get burned by doing no statistics maintenance at all if query performance stats to get slow and out of date statistics are found to be the cause.
You can check that setting on your databases with the query: You should leave auto_create_stats_on set to 1 unless an application is specifically designed to manually create its own statistics.
(That is pretty much limited to weirdoes like Share Point.) In rare situations, manually creating a multi-column statistic or a filtered statistic can improve performance…
This trace flag changes the locking behavior associated with updating statistics.