What operations result in automatic creation of SQL Server statistics?

All we need is an easy explanation of the problem, so here it is.

My understanding is that creating an index automatically creates statistics for that column.

What else results in auto creation of statistics? For example – Does SQL Server create statistics for every column that has ever appeared in a WHERE clause?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

AUTO_CREATE_STATISTICS Option

When the automatic create statistics option, AUTO_CREATE_STATISTICS is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan.

These single-column statistics are created on columns that do not already have a histogram in an existing statistics object.

When the Query Optimizer creates statistics as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA.

You can use the following query to determine if the Query Optimizer has created statistics for a query predicate column.

SELECT OBJECT_NAME(s.object_id) AS object_name,  
    COL_NAME(sc.object_id, sc.column_id) AS column_name,  
    s.name AS statistics_name  
FROM sys.stats AS s 
INNER JOIN sys.stats_columns AS sc  
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id  
WHERE s.name like '_WA%'  
ORDER BY s.name;

That includes WHERE predicate:

SELECT *
FROM tab
WHERE col = ?;    -- here tab.col

and JOIN predicate(to determine physical join type: NESTED LOOPS/MERGE/HASH):

SELECT *
FROM tab1
JOIN tab2 
  ON tab1.id = tab2.tab_1_id; -- here tab2.tab_1_id

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply