For details on one or two other cases where SQL Server will update statistics, please see Statistics Used by the Query Optimizer in Microsoft SQL Server 2008.What this means is that over time, as business processes modify data in a table, the associated statistics can become progressively ‘stale’ and less accurate in their representation of the data.While many recognized that indexes and statistics were two distinct entities, they didn’t always understand when each would update.As we add, delete or modify rows in a table, SQL Server adds, deletes or modifies corresponding rows in the indexes.
Based on this knowledge, it decides the optimal access path, making choices such as whether to scan a table or perform an index seek, use a nested loop join or a hash join, and so on.
For example, if we modify the value for (covered in more detail later in the article).
In general, when 20% of the rows in a table, plus 500, have been modified, SQL Server considers the associated column and/or index statistics objects to be ‘stale’.
in the search condition of a command ( to create single- and multi-column statistics manually.
In essence, statistics are a histogram describing the data distribution in a column.
I’ll then explain, in more detail, why automatic updates to statistics may not be sufficient, and what data you can gather and monitor to help you manage statistics manually, and proactively, rather than in response to query performance issues.