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.
If you’re interested in that, head on over and read the fahhhbulous white paper, Statistics Used by the Query Optimizer in SQL Server 2008. Unfortunately, Aristotle was right when it comes to statistics maintenance in SQL Server. This is because you didn’t do any proactive work at all.
I’m also not talking about statistics for memory optimized tables in this article. Back when I read philosophy, I found Aristotle a bit annoying because he talked so much about “moderation”. You shouldn’t run statistics maintenance against a database at the same time you’re checking for corruption, rebuilding indexes, or running other IO intensive processes.
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.
) 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.
Prior to 2016, here’s a quick rundown of pros and cons of TF2371: Overall, this is a low risk trace flag.
But in general it does NOT pay off to enable trace flags “just in case” for most people.
Trace Flag 7471 is a global trace flag released in SQL Server 2014 SP1 Cumulative Update 6.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.If you’re managing lots of SQL Server instances and want ultimate customization, there is a free version of Minion Reindex. The answers to both of these questions impact what it does to GROUP those rows and SUM the Name Count column.Statistics are small, lightweight objects that describe the distribution of data in a SQL Server table. First Name By Year and you run this query: SQL Server needs to estimate how many rows will come back for First Name Id=74846. Statistics are lightweight little pieces of information that SQL Server keeps on tables and indexes to help the optimizer do a good job. First Name By Year table was freshly created when we ran our query, it would have no column statistics.Because why optimize a query on bad data estimates?