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?