SQL Server: Outdated Statistics Can Mislead Query Optimizer

Recently a friend of mine asked that how to detect that statistics are not being updated and due to these outdated statistics query optimizer is being misguided for proper plan selection.

 

SET AUTO_UPDATE STATISTICS ON, will automatically update your database statistics but there are possibilities that somehow statistics are not being updated properly. Or proper statistics are missing.

 

To check, whether statistics are being updated properly or not, execute your query and get graphical query plan, move your mouse to physical operator node of your plan and check details in yellow popup page.

 

 Keep eye on “Actual Number of Rows” and “Estimated Number of Rows”, values which should be almost same. If the difference is too large it’s sure that statistics are not being updated properly or statistics don’t exists and query optimizer will not to be able to select proper plan for your query.

 

It’s worthy to note that auto update statistics occur only if:

• Number of rows in a table are 10000 as min. 
• Percent of update on table are more than 10%