15 August,2012 by Jack Vamvas
Question: Usually when I look at the statistics information on an index , SQL statistics are available. I was troubleshooting a query performance issue today – and noticed no statistics under heavily used objects. What could be causing this problem, as I understand the DB engine maintains the statistics about the key value distribution, and uses the statistics for query plan compilation.?
Answer: Check the status of AUTO CREATE STATISTICS. If it’s OFF , then most likely someone has turned it off. When AUTO CREATE STATISTICS is set at OFF , a manual process must be set up to create statistics.
Normally I have AUTO CREATE STATISTICS at ON. This means the SQL Query Optimizer creates the statistics on individual columns for cardinality improvement.