New SQL 2008 R2 SP1 trace flag adjusts autostats threshold

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. 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

Share

2 Responses

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?