Database Reference
In-Depth Information
Auto Create Statistics
The auto create statistics feature automatically creates statistics on nonindexed columns when referred to in the
WHERE
clause of a query. For example, when this
SELECT
statement is run against the
Sales.SalesOrderHeader
table on a
column with no index, statistics for the column are created.
SELECT cc.CardNumber,
cc.ExpMonth,
cc.ExpYear
FROM Sales.CreditCard AS cc
WHERE cc.CardType = 'Vista';
Then the auto create statistics feature (make sure it is turned back on if you have turned it off ) automatically
creates statistics on column
CardType
. You can see this in the Extended Events session output in Figure
12-28
.
Figure 12-28.
Session output with
AUTO_CREATE_STATISTICS ON
The auto_stats event fires to create the new set of statistics. You can see the details of what is happening in the
statistics_list
field: “Created: CardType.”
Auto Update Statistics
The auto update statistics feature automatically updates existing statistics on the indexes and columns of a permanent
table when the table is referred to in a query, provided the statistics have been marked as out-of-date. The types of
changes are action statements, such as
INSERT
,
UPDATE
, and
DELETE
. The default threshold for the number of changes
depends on the number of rows in the table, as shown in Table
12-4
.
