Regularly Update Statistics for Ascending Keys


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 (3)

  1. msdngtnexus says:

    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.

  2. Dave M says:

    Very useful information. Thanks Ian and keep up the good work.