Trace flag 2371

26 בספטמבר 2011

4 comments

I know many DBAs that are not yet aware of this trace flag and will certainly be very happy to hear about it as they can get an immediate benefit. I just run into it recently at this post and plan to implement soon.

Trace flag 2371 finally comes in to address the well know problem of the current 20% threshold used in the automatic update of statistics.

The source to the problem is that statistics are updated when 20% (+ 500 rows) of the data has changed. This means that a table with 10,000 rows will cross the threshold at 2,500 rows and a table with 100,000 rows will cross the threshold at 20,500 rows and so on. Once the threshold is crossed the statistics are marked as invalidated and remain in this state until the next time the Optimizer needs the statistics when generating an execution plan.

While this may be a good choice in most cases it becomes problematic when dealing with large tables. As the table size grows the 20% figure increases accordingly and so does the time interval between each time the threshold is crossed. This leads to stale statistics resulting in suboptimal execution plans which in large sets of data can be a disaster.

Therefore in such environments DBAs typically create their own update statistics job to get the stats manually updated thus eliminating the need to rely on crossing the 20% threshold of the auto update of stats. With the new 2371 trace flag these jobs that generate a lot of IO will no longer be needed.

For additional information on statistics check out the Microsoft article Statistics Used by the Query Optimizer in Microsoft SQL Server and this post at sqlskills.

-Yaniv

 

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

4 comments

  1. Yaniv10 בנובמבר 2011 ב 17:59

    Forgot to mention but Trace flag 2371 is first available  at SQL Server 2008R2 sp1.

    Reply
  2. Babcock8 ביולי 2012 ב 1:31

    It¦s actually a great and useful piece of information.

    I¦m happy that you only shared this useful info with us.
    Please stay us informed such as this. Thanks for sharing.

    Reply
  3. Elam11 ביולי 2012 ב 0:22

    I am usually to blogging and i also truly appreciate your articles.
    This great article has truly peaks my interest.
    I will bookmark your site and keep checking for first time info.

    Reply