Statistics - Pro SQL Server Internals - page 68

join sys.schemas sc on
t.schema_id = sc.schema_id
outer apply
sys.dm_db_stats_properties(t.object_id,s.stats_id) p
where
sc.name = 'dbo' and t.name = 'Books'
The result of the query shown in Figure 3-12 indicates that there were 250,000 modifications in the statistics
columns since the last statistics update. You can build a statistics maintenance routine that regularly checks the
sys.dm_db_stats_properties DMV and rebuilds statistics with large modification_counter values.
Figure 3-12. Sys.dm_db_stats_properties output
Another statistics-related database option is Auto Update Statistics Asynchronously . By default, when SQL Server
detects that statistics are outdated, it stops query execution, synchronously updates statistics, and generates a new
execution plan after the statistics update is complete. With asynchronous statistics update, SQL Server executes the
query using the old execution plan, which is based on outdated statistics while updating statistics in background
asynchronously. It is recommended that you keep synchronous statistics update unless the system has a very small
query timeout, in which case a synchronous statistics update can timeout the queries.
There is the trace flag 2371, which was introduced in SQL Server 2008R2 SP1. This flag changed the threshold
of the number of changes when statistics become outdated, making it dynamic. More rows are in the table, and a
smaller number of modifications are required to outdate the statistics. Even though this trace flag is mentioned in the
MSDN blog at: http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-
statistics-in-sql-server-traceflag-2371.aspx , it is not documented in Books Online. Use it with care, and test it
before deploying to production.
Finally, SQL Server does not drop column-level statistics automatically when you create new indexes. You should
drop redundant column-level statistics objects manually.
SQL Server 2014 Cardinality Estimator
As you already know, the quality of query optimization depends on accurate cardinality estimations. SQL Server
must correctly estimate the number of rows in each step of query execution to generate an efficient execution plan.
The cardinality estimation model used in SQL Server 2005-2012 was initially developed for SQL Server 7.0 and
released in 1998. Obviously, there were some minor improvements and changes in the newer versions of SQL Server;
however, conceptually, the model remains the same.
There are four major assumptions used in the model, including:
Uniformity: This model assumes uniform data distribution in the absence of statistical
information. For example, inside histogram steps, it is assumed that all key values are to be
distributed evenly and uniformly.
Independence: This model assumes that attributes in the entities are independent from
each other. For example, when a query has several predicates against different columns of
the same table, it assumes that the columns are not related in any way.