SQL Server with Mr. Denny

Oct 4 2011   7:58PM GMT

New SQL 2008 R2 SP1 trace flag adjusts autostats threshold



Posted by: Denny Cherry
Tags:
auto-stats
SQL Server
SQL Server 2008 R2
SQL Server 2012
Statistics
Trace Flag

I recently learned about a new trace flag which has been introduced in SQL Server 2008 R2 SP1 (and SQL Server “Denali”).  This trace flag, number 2371, changes the way that the SQL Server figures out when auto-stats should kick in.

Up until now auto-stats was fixed to kick in every time that the column (or table in the older versions) had 20% + 500 rows change.  With the new flag turned on auto stats now stats static at the old value up until the row has 25,000 rows in it.  At this point the percentage of rows that need to change before auto-stats kicks in gets smaller and smaller as the table grows.Click To Enlarge As you can see from the included graph as the number of rows in the table gets higher the percentage of rows that needs to changes gets very small with tables in the 100M row range needing only 0.31% of the rows to change (310,000 rows).  Because of this the odds of a large table having auto stats kick in is much greater than before, in the range of 20 to 60 times greater according to the SAP on SQL Server blog (the same place I stole the graph from).  As Microsoft points out in their blog post by updating stats this much more frequently new stats could be generated in just a couple of days instead of waiting for weeks or months for new stats to be generated.

Like everything in SQL Server, nothing is free.  Keep in mind that when auto-stats kicks in for a column or table all the execution plans for that table or column will be invalidated and new plans will be generated.  If auto-stats were to kick in, in the middle of the day this could cause performance problems while the new plans are being generated which would be seen as slow query run times and increased CPU load on the SQL Server as well as increased IO load when you aren’t expecting it as update stats goes through reading the data in the table.

This trace flag was specifically designed for the “stupid large” databases in the world that are running OLTP workloads such as when running SAP on SQL Server which can generate some insanely large OLTP tables.

Microsoft currently doesn’t have an recommendations on when to turn on this trace flag, as finding companies with “stupid large” databases to test stuff on is tricky at best.

If you have one of these “stupidly large” databases and you find yourself having to manually update stats (probably via a scheduled job unless you really hate yourself) and you have SQL Server 2008 R2 SP1 installed then you might want to check out this trace flag and see if it helps you out.  Personally I’ve got a system I’d love to try this out on, and I’ll be getting that system upgraded to SP1 shortly so I can give it a try.

I wouldn’t expect this trace flag to be back ported down to SQL Server 2008 or SQL Server 2005.

Denny

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    [...] 2008 R2 SP1 is the ability to change the auto stats algorithm from the default of 20%+500 rows to a sliding scale.  This feature is only available by turning on trace flag 2371 so it won’t be turned on by [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: