Because why optimize a query on bad data estimates?
The thresholds for when auto-update statistics kicks in are a little complicated.
Here’s what the statistics look like on the table in Object Explorer. If you want to verify which column is in each auto-created statistic, you can do that with this query: SELECT s.name, s.auto_created, s.user_created, as colname FROM sys.stats AS s JOIN sys.stats_columns as sc on s.stats_id=sc.stats_id and s.object_id=sc.object_id JOIN sys.columns as c on sc.object_id=c.object_id and sc.column_id=c.column_id WHERE s.object_id=OBJECT_ID('agg.
First Name By Year') and s.auto_created = 1 ORDER BY sc.stats_column_id; GO Sure enough, here are our statistics, and they are on Gender and First Name Id.
) Trace flag 2371 is available in SQL Server 2008R2 SP1-SQL Server 2014. Erik Darling tested out the behavior in 2016 and wrote about it here.
If you have multiple SQL Servers using shared storage, that maintenance may hit the storage at the same time. ⇒ The moderate approach: One widely used free script is Ola Hallengren’s SQL Server Index and Statistics Maintenance script.
Even if you have a statistic on every column in the table, this is a very small amount of overhead.