Database Reference
In-Depth Information
SELECT Test1.Test1_C2,
Test2.Test2_C2
FROM dbo.Test2
JOIN dbo.Test1
ON Test1.Test1_C2 = Test2.Test2_C2
WHERE Test1.Test1_C2 = 2;
Figure 12-14.
Execution plan with AUTO_CREATE_STATISTICS OFF (a variation)
You can see that turning off the auto create statistics feature has a negative effect on performance by comparing
the cost of this query with and without statistics on a nonindexed column. Table
12-3
shows the difference in the cost
of this query.
Table 12-3.
Cost Comparison of a Query With and Without Statistics on a Nonindexed Column
Statistics on Nonindexed Column
Figure
Cost
Duration (ms)
Number of Reads
With statistics
Figure
12-11
98
48
Without statistics
Figure
12-13
262
20273
The number of logical reads and the CPU utilization are higher with no statistics on the nonindexed columns.
Without these statistics, the optimizer can't create a cost-effective plan because it effectively has to guess at the
selectivity through a set of built-in heuristic calculations.
A query execution plan highlights the missing statistics by placing an exclamation point on the operator that
would have used the statistics. You can see this in the clustered index scan operators in the previous execution plans
(Figures
12-12
and
12-14
), as well as in the detailed description in the Warnings section in the properties of a node in
a graphical execution plan, as shown in Figure
12-15
for table
Test1
.
