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:
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite penciles.
No comments:
Post a Comment
All suggestions are welcome