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
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=1018Anonymous
November 06, 2012
Very useful information. Thanks Ian and keep up the good work.