Regularly Update Statistics for Ascending Keys

  • Article

Ascending key columns, such as IDENTITY columns or datetime columns representing real-world timestamps, can cause inaccurate statistics in tables with frequent INSERTS because new values all lie outside the histogram. Consider updating statistics on such columns frequently with a batch job if your application seems to be getting inadequate query plans for queries that have a condition on the ascending key column. How often to run the batch job depends on your application. Consider daily or weekly intervals, or more often if needed for your application. Alternatively, trigger the job based on an application event, such as after a bulk load or after a certain number of INSERT operations.

Comments

  • Anonymous
    October 22, 2007
    Is there anyway to get to the statistics histogram and/or the stats header information without using dbcc show_statistics. I would like to run some queries across all the stats objects in the database? I looked for some DMVs to do this but couldnt find any. Thanks.

  • Anonymous
    June 18, 2009
    PingBack from http://firepitidea.info/story.php?id=1018

  • Anonymous
    November 06, 2012
    Very useful information. Thanks Ian and keep up the good work.