>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%
Advertisements

Posted on January 13, 2011, in Performance Tuning, Sql Server 2005, Sql Server 2008. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: