Database Reference
In-Depth Information
3.
Disable the auto create statistics feature by deselecting the Auto Create Statistics check box
for the corresponding database or by executing the following SQL command:
ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS OFF;
Now reexecute the
SELECT
statement
--nonindexed_select
.
SELECT Test1.Test1_C2,
Test2.Test2_C2
FROM dbo.Test1
JOIN dbo.Test2
ON Test1.Test1_C2 = Test2.Test2_C2
WHERE Test1.Test1_C2 = 2;
Figure
12-12
and Figure
12-13
show the resultant execution plan and Extended Events output, respectively.
Figure 12-12.
Execution plan with AUTO_CREATE_STATISTICS OFF
Figure 12-13.
Trace output with AUTO_CREATE_STATISTICS OFF
With the auto create statistics feature off, the query optimizer selected a different execution plan compared to the
one it selected with the auto create statistics feature on. On not finding statistics on the relevant columns, the optimizer
chose the first table (
Test1
) in the
FROM
clause as the outer table of the nested loop join operation. The optimizer couldn't
make its decision based on the actual data distribution in the column. You can see the warning, an exclamation point, in
the execution plan, indicating the missing statistics information on the data access operators, the clustered index scans.
If you modify the query to reference table
Test2
as the first table in the
FROM
clause, then the optimizer selects table
Test2
as the outer table of the nested loop join operation. Figure
12-14
shows the execution plan.
