Database Reference
In-Depth Information
Figure 12-38.
Actual and estimated number of rows with up-to-date statistics
The optimizer accurately estimated the number of rows using updated statistics and consequently was able
to come up with a more efficient plan. Since the estimated number of rows is 1, it makes sense to retrieve the row
through the nonclustered index on
C1
instead of scanning the base table.
Updated, accurate statistics on the index key column help the optimizer come to a better decision on the
processing strategy and thereby reduce the number of logical reads from 84 to 4 and reduce the execution time from
16ms to
-
0ms (there is a
-
4ms lag time).
Before continuing, turn the statistics back on for the database.
ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS ON;
Recommendations
Throughout this chapter, I covered various recommendations for statistics. For easy reference, I've consolidated and
expanded upon these recommendations in the sections that follow.
Backward Compatibility of Statistics
Statistical information in SQL Server 2014 can be generated differently from that in previous versions of SQL Server.
However, SQL Server 2014 transfers the statistics during upgrade and, by default, automatically updates these
statistics over time as the data changes. For the best performance, however, I suggest manually update the statistics
immediately after an upgrade, preferably, where possible, using
FULLSCAN
.
Auto Create Statistics
This feature should usually be left on. With the default setting, during the creation of an execution plan, SQL Server
determines whether statistics on a nonindexed column will be useful. If this is deemed beneficial, SQL Server creates
statistics on the nonindexed column. However, if you plan to create statistics on nonindexed columns manually, then
you have to identify exactly for which nonindexed columns statistics will be beneficial.
