Database Reference
In-Depth Information
There is overhead associated with column-level statistics maintenance, although it is much smaller than that of
the index, which needs to be updated every time data modifications occur. In some cases, when particular queries do
not run very often, you can elect to create column-level statistics rather than an index. Column-level statistics help
Query Optimizer find better execution plans, even though those execution plans are suboptimal due to the index
scans involved. At the same time, statistics do not add an overhead during data modification operations, and they
help avoid index maintenance. This approach works only for rarely executed queries, however. You need to create
indexes to optimize queries that run often.
Statistics and Execution Plans
SQL Server creates and updates statistics automatically by default. There are two options on the database level that
control such behavior:
1.
Auto Create Statistics
controls whether or not optimizer creates
column-level
statistics
automatically. This option does not affect index-level statistics, which are always created.
The
Auto Create Statistics
database option is enabled by default.
2.
When the
Auto Update Statistics
database option is enabled, SQL Server checks if statistics
are outdated every time it compiles or executes a query and updates them if needed.
The
Auto Update Statistics
database option is also enabled by default.
■
You can control statistics auto update behavior on the index level by using the
STATISTICS_NORECOMPUTE
index
option. By default, this option is set to OFF, which means that statistics are automatically updated. another way to change
auto update behavior at the index or table level is by using the
sp_autostats
system stored procedure.
Tip
SQL Server determines if statistics are outdated based on the number of changes that affect the statistics columns
performed by the
INSERT
,
UPDATE
,
DELETE
, and
MERGE
statements. There are three different cases, called
statistics update
thresholds,
also sometimes known as
statistics recompilation thresholds,
when SQL Server marks statistics as outdated.
1.
When a table is empty, SQL Server outdates statistics when you add data to an
empty table.
2.
When a table has less than 500 rows, SQL Server outdates statistics after every 500 changes
of the statistics columns.
3.
When a table has 500 or more rows, SQL Server outdates statistics after every 500 + (20% of
total number of rows in the table) changes of the statistics columns.
It is also worth mentioning that SQL Server counts how many times the statistics columns were changed,
rather than the number of changed rows. For example, if you change the same row 100 times, it would be counted as
100 changes rather than as 1 change.
That leads us to a very important conclusion. The number of changes to statistics columns required to trigger a
statistics update is proportional to the table size. The larger the table, the less often statistics are automatically updated.
For example, in the case of a table with 1 billion rows, you would need to perform about 200 million changes to
statistics columns to make the statistics outdated. Let's look how that behavior affects our systems and execution plans.
■
SQL Server 2014 introduces a new cardinality estimation model, which changes cardinality estimations for the
case shown below. We will talk about it in detail later in the chapter.
Note
