Friday, June 7, 2013

MSSQL: Plan guides to address ascending key problem, or increased stats attention?

Ok... lets start with the questions: In SQL Server, the ascending key problem can be addressed with statistics trace flags (such as 2371, 2389, and 2390), or increased manual stats update... but are plan guides also an appropriate mitigation?  If you've used plan guides for this purpose, how have evaluated them over time?


Now I'll meander...



On the systems I'm concerned with, many primary keys include at least one field which consistently ascends over time.  Often this is an identity type field (an identifier for the same record/object/data in a separate system) or a field based on datetime.  At the same time, the queries on this system focus heavily on the most recent data.  Queries with a scope of one day are more frequent than those with scope of one week, which are more frequent than those with scope of one month.  Any other query scope is far less frequent than monthly.  Most data is retained for at least 7 years if not indefinitely.


in this context, and especially with some of the complex queries running, the ascending key problem is a particular vulnerability.


Others have explained this issue far better than I; the blog posts below can be consulted for a more complete description so you can evaluate whether your system is effected.  


In a nutshell - the statistics histogram only knows about key values up to the highest value as of the last stats update.  The modification threshold for auto stats update scales with table row count.  Got a table that grows by a fairly constant row count over time?  Auto updates will become less and less frequent.  With row count in the billions, you'll wait a long time for auto stats update to occur - much longer than the inter-update interval while your table had millions of rows.

And the killer: since the histogram only knows of the highest value as of last update, all the rows added since then are absent from the histogram.  Here's a Sherlock Holmes type clue: if the query plan shows 'estimated rows' of 1, and you know doggone well there's thousands in the range, you could well be looking at manifestation of the ascending key problem.  Just saw a complex query, with four tables among others in the join list, a similar pk in each of the four, and an 'estimated rows' of 1 for each.  Counting the rows after the biggest range hi key in the stats yielded over 300,000 rows in each case!  Major performance buster.


The classic solution is to update all statistics all the dog-gone time.  Just like the classic index maintenance solution is to rebuild all indexes all the dog-gone time.  The pitfall is the same: you'll end up performing lots of low value, high cost operations.  Lots of disk io wasted.  Lots of plans which will be recompiled, maybe, and maybe little benefit to most of the new plan compiles.  And.... this is the one that always breaks my heart... what if the manual stats update happened on Monday according to schedule, but the query is run on Saturday and performance SUCKS because there are 70,000 rows in the range above the last known range hi key?


Ok... one way to address this in part is with trace flags 2389, 2390, and 2371.


Trace flag 2371 introduces a sliding threshold for automatic statistics update.

http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

What's nice about this is that it will increase auto stats updates for humongous tables that are NOT based on an ascending pk.  I got lots of those, too :) TF 2371 helps these systems out.  But... ymmv.  Some tables are such that data distribution is irregular by design, and default sampling isn't good for optimal plan selection.  Coordinating full scan stats update will be better in that case.


Trace flags 2389 and 2390 are meant specifically to address the ascending key problem.  After three statistics updates with trace flag 2389 and/or 2390 enabled , indexes are branded as ascending, stationary, etc.  With trace flag 2389 in place, quick stats updates will lead to improved row estimates for ascending key statistics.  With trace flag 2390 in place, quick stats are enabled for 'unknown' keys as well (3 stats updates haven't occurred for these).  These have been beneficial in the systems I work with... but finding a way to quantify the batch-wide benefit is tricky.

http://www.benjaminnevarez.com/2013/02/statistics-on-ascending-keys/

https://www.simple-talk.com/sql/database-administration/statistics-on-ascending-columns/

http://blogs.msdn.com/b/ianjo/archive/2006/04/24/582227.aspx


For a strong finish, I'll circle back to my questions: In SQL Server, the ascending key problem can be addressed with statistics trace flags (such as 2371, 2389, and 2390), or increased manual stats update... but are plan guides also an appropriate mitigation?  If you've used plan guides for this purpose, how have evaluated them over time?

 

5 comments:

  1. Well, shoot! If no-one else will comment, I will! Plan guides are one of two features that Brent Ozar called out in his March 2010 post "2 Features of SQL Server You Should Avoid" http://www.brentozar.com/archive/2010/03/2-features-of-sql-server-you-should-avoid/

    But if the choices are relying on trace flags, relying on frequent manual stats updates, and plan guides...

    ReplyDelete
  2. I was so excited to see a comment on this post on the blogger summary page... forgot it was just me commenting :)

    ReplyDelete
  3. I wouldn't entirely dismiss a feature because the Ozar or any other team said so. I would capture metrics, test, and capture more metrics. Let the statistics tell you what to do. Let the seasoned DBA's guide you, but not dictate your ultimate solution. Great post!

    ReplyDelete
    Replies
    1. Thanks for the comment, Allen! With all of the work I've done with trace flags, I don't scare easily :) I hope that plan guides or similar features become more mature in future SQL Server development. Because I also work with Oracle, I have high hopes for the Plan Management features available in Oracle 12c, including Adaptive Plans (evaluation of in-progress plan behavior against the initial expectations of the cost-based optimizer, to determine if a do-over is necessary) and Plan Promotion.
      I'm also interested in using plan guides to add querytraceon to a given query or stored procedure, as @SpaghettiDBA does here:
      http://spaghettidba.com/2013/02/08/using-querytraceon-in-plan-guides/

      Delete
  4. I wish I would have read this post back in 2013... things would be much nicer in my part of the world today if I had. :( :)

    ReplyDelete