Statistics in SQL Server - Microsoft SQL Server 2012 Performance Tuning - page 290

How it works...
If the query optimizer gets the statistics of each column used in predicate, it will have better a
idea of the number of rows going to return, based on predicate, and will also know the pattern
of data that helps the query optimizer in selecting the best route to execute your query. This
will result in good execution, and we can see its effect via the execution plan generated.
Find out-of-date statistics and get it correct
The statistics object is the major source of information about data distribution for the
predicate. Without knowing the exact data distribution, the query optimizer cannot have
cardinality estimation, which is the process of calculating number of rows to return by
applying the predicate.
After creating the statistics for the column, the column becomes out-of-date after executing
DML commands, such as INSERT , UPDATE , and DELETE , because these commands change
data, thereby affecting data distribution. In this scenario, a statistics update is needed.
In highly active tables, statistics become outdated in maybe a few hours; for static tables,
statistics become outdated maybe in a few weeks. The decision about out-of-date statistics
totally depends on the DML statements executed on the table
Before we move back to the core subject and start writing down the script, it is mandatory to
draw attention to some facts and also to look at flashbacks.
Till SQL Server 2000, the query optimizer used to track each insert, update, and delete
operation on the table and increment the value of the RowModCtr (Row Modification Counter)
column in the SysIndexes system view. As soon as statistics get updated, RowModCtr
reinitializes the value and starts count from zero again. So, by looking at the SysIndexes
system view and the value of the RowModCtr column, we get to know whether statistics are
out-of-date or not.
After SQL Server 2000, SQL Server Engine has changed the pattern of capturing the value
of modification in tables. Rather than capturing each insert, update, and delete operation
for every row, it now counts modifications made to each column and stores them. The
query optimizer decides whether statistics are out-of-date or not, based on values stored
for ColModCtr . Fortunately or unfortunately, ColModCtr is hidden for the user by all
documented system views.
But the good news is, Sys.SysIndexes is still available in SQL Server 2012 so we can even
use RowModCtr to decide whether statistics are out-of-date or not. Though RowModCtr is not
as accurate as ColModCtr but it is better to have something rather than nothing.