Database Reference
In-Depth Information
You can see that turning off the auto update statistics feature has a negative effect on performance by comparing
the cost of this query with and without updated statistics. Table
12-1
shows the difference in the cost of this query.
Table 12-1.
Cost of the Query With and Without Updated Statistics
Statistics Update Status
Figure
Cost
CPU (ms)
Number of Reads
Updated
Figure
12-4
63
34
Not updated
Figure
12-6
96
1514
The number of logical reads and the CPU utilization are significantly higher when the statistics are out-of-date,
even though the data returned is identical and the query was precisely the same. Therefore, it is recommended
that you keep the auto update statistics feature on. The benefits of keeping statistics updated outweigh the costs of
performing the update. Before you leave this section, turn
AUTO_UPDATE_STATISTICS
back on (although you can also
manually update statistics if you choose).
ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS ON;
Statistics on a Nonindexed Column
Sometimes you may have columns in join or filter criteria without any index. Even for such nonindexed columns, the
query optimizer is more likely to make the best choice if it knows the cardinality and data distribution, also known
as the
statistics
, of those columns.
In addition to statistics on indexes, SQL Server can build statistics on columns with no indexes. The information
on data distribution, or the likelihood of a particular value occurring in a nonindexed column, can help the query
optimizer determine an optimal processing strategy. This benefits the query optimizer even if it can't use an index
to actually locate the values. SQL Server automatically builds statistics on nonindexed columns if it deems this
information valuable in creating a better plan, usually when the columns are used in a predicate. By default, this
feature is turned on, and it's configurable through the Properties
➤
Options
➤
Auto Create Statistics setting of a
database. You can override this setting programmatically by using the
ALTER DATABASE
command. However, for better
performance, it is strongly recommended that you keep this feature on.
In general, you should not disable the automatic creation of statistics on nonindexed columns. One of the
scenarios in which you may consider disabling this feature is while executing a series of ad hoc SQL activities that
you will not execute again. Even in such a case, you should test whether you're better off paying the cost of automatic
statistics creation to get a better plan in this one case as compared to affecting the performance of other SQL Server
activities. So, for most systems, you should keep this feature on and not be concerned about it.
Benefits of Statistics on a Nonindexed Column
To understand the benefit of having statistics on a column with no index, create two test tables with disproportionate
data distributions, as shown in the following code. Both tables contain 10,001 rows. Table
Test1
contains only one row
for a value of the second column (
Test1_C2
) equal to 1, and the remaining 10,000 rows contain this column value as 2.
Table
Test2
contains exactly the opposite data distribution.
