Database Reference
In-Depth Information
To understand the detrimental effect of having outdated statistics, follow these steps:
1.
Re-create the preceding test table with 1,500 rows only and the corresponding
nonclustered index.
2.
Prevent SQL Server from updating statistics automatically as the data changes. To do so,
disable the auto update statistics feature by executing the following SQL statement:
ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS OFF;
3.
Add 1,500 rows to the table like before.
Now, reexecute the
SELECT
statement to understand the effect of the outdated statistics on the query optimizer.
The query is repeated here for clarity:
SELECT *
FROM dbo.Test1
WHERE C1 = 2;
Figure
12-5
and Figure
12-6
show the resultant execution plan and the session output for this query, respectively.
Figure 12-5.
Execution plan with AUTO_UPDATE_STATISTICS OFF
Figure 12-6.
Session output details with AUTO_UPDATE_STATISTICS OFF
With the auto update statistics feature switched off, the query optimizer has selected a different execution plan
from the one it selected with this feature on. Based on the outdated statistics, which have only one row for the filter
criterion (
C1 = 2
), the optimizer decided to use a nonclustered index seek. The optimizer couldn't make its decision
based on the current data distribution in the column. For performance reasons, it would have been better to hit the
base table directly instead of going through the nonclustered index, since a large result set (1,501 rows out of 3,000
rows) is requested.
