snippetsqlModerate
How SQL server generates a Query plan with Auto Create Statistics set to OFF
Viewed 0 times
generatescreatesqlwithqueryautooffplanstatisticshow
Problem
I am wondering how SQL server can generate a query plan with "Auto create statistics" set to "off".
I stumbled on the below context at the below link while searching for an answer(Sorry, I searched, but couldn't find anything better).
https://www.simple-talk.com/sql/performance/execution-plan-basics/
Table variables do not ever have statistics generated on them, so they
are always assumed by the optimizer to have a single row, regardless
of their actual size
Even though the above is applicable only for Table variables, I thought SQL Server will use the same principle to derive a query plan for a table and decided to do a small test.
I cleared the PROC cache and Ran the below query on a table with 1 million records
My test proved me wrong and SQL server reported missing statistics(as expected), But came up with the estimated number of rows as 31622(I expected it to be 1) when there are no statistics. How did the query optimizer come up with this value without statistics?
I stumbled on the below context at the below link while searching for an answer(Sorry, I searched, but couldn't find anything better).
https://www.simple-talk.com/sql/performance/execution-plan-basics/
Table variables do not ever have statistics generated on them, so they
are always assumed by the optimizer to have a single row, regardless
of their actual size
Even though the above is applicable only for Table variables, I thought SQL Server will use the same principle to derive a query plan for a table and decided to do a small test.
I cleared the PROC cache and Ran the below query on a table with 1 million records
My test proved me wrong and SQL server reported missing statistics(as expected), But came up with the estimated number of rows as 31622(I expected it to be 1) when there are no statistics. How did the query optimizer come up with this value without statistics?
Solution
Table variables is another story, what the server uses when there are no statistics is estimation for unknown input:
So in your case the estimation will be select power(1000000, 0.75) = 31622
( = predicate with non unique column prior to 2014)
Related book: T-SQL Querying by Itzik Ben-Gan
So in your case the estimation will be select power(1000000, 0.75) = 31622
( = predicate with non unique column prior to 2014)
Related book: T-SQL Querying by Itzik Ben-Gan
Context
StackExchange Database Administrators Q#176132, answer score: 10
Revisions (0)
No revisions yet.