Statistics, Data Distribution, and Cardinality - SQL Server Query Performance Tuning - page 202

The resultant session output doesn't perform any additional SQL activities to manage statistics. The statistics
on the nonindexed columns ( Test1.Test1_C2 and Test2.Test2_C2 ) had already been created when the indexes
themselves were created and updated as the data changed.
For effective cost optimization, in each case the query optimizer selected different processing strategies,
depending upon the statistics on the nonindexed columns ( Test1.Test1_C2 and Test2.Test2_C2 ). You can see
this from the previous two execution plans. In the first, table Test1Test1 is the outer table for the nested loop join,
whereas in the latest one, table Test2 is the outer table. By having statistics on the nonindexed columns ( Test1.
Test1_C2 and Test2.Test2_C2 ), the query optimizer can create a cost-effective plan suitable for each case.
An even better solution would be to have an index on the column. This would not only create the statistics on
the column but also allow fast data retrieval through an Index Seek operation, while retrieving a small result set.
However, in the case of a database application with queries referring to nonindexed columns in the WHERE clause,
keeping the auto create statistics feature on still allows the optimizer to determine the best processing strategy for the
existing data distribution in the column.
If you need to know which column or columns might be covered by a given statistic, you need to look into the
sys.stats_columns system table. You can query it in the same way as you did the sys.stats table.

SELECT *
FROM sys.stats_columns
WHERE object_id = OBJECT_ID('Test1');

This will show the column being referenced by the automatically created statistics. You can use this information
to help you if you decide you need to create an index to replace the statistics because you will need to know which
columns to create the index on. The column listed here is the ordinal position of the column within the table. To see
the column name, you'd need to modify the query.

SELECT c.name,
sc.object_id,
sc.stats_column_id,
sc.stats_id
FROM sys.stats_columns AS sc
JOIN sys.columns AS c
ON c.object_id = sc.object_id
AND c.column_id = sc.column_id
WHERE sc.object_id = OBJECT_ID('Test1');

Drawback of Missing Statistics on a Nonindexed Column
To understand the detrimental effect of not having statistics on nonindexed columns, drop the statistics automatically
created by SQL Server and prevent SQL Server from automatically creating statistics on columns with no index by
following these steps:

1. Drop the automatic statistics created on column Test1.Test1_C2 through the Manage

Statistics dialog box, as shown in the section “Benefits of Statistics on a Nonindexed
Column,” or use the following SQL command, substituting the system name automatically
given the statistics for the phrase StatisticsName :
DROP STATISTICS [Test1].StatisticsName;

2. Similarly, drop the corresponding statistics on column Test2.Test2_C2 .