No statistics management options fit nightly load large ETL system scenario by Custom22

See http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/d32bdad8-eb84-4a64-a061-e657744083cf

I've copied my post on that forum here;

I use SQL server 2008 R1, Enterprise.

We have nightly ETL processes which insert incrementally into large databases (2-6TB). The main tables are all sequentially ID'd by our loading software (i.e. it controls the incrementing primary keys, not SQL server)

I'm trying to figure out ways to make our statistics more efficient.

Auto create stats is on, Auto update stats is on, but Auto update stats async is off.

However, what we find is that even though auto update stats is on, it is necessary for us to run sp_updatestats as part of the nightly load. If we don't, SQL server comes up with inaccurate explain plans, especially around the newly inserted data. I've read that auto update stats won't do anything if SQL server estimates that it would be quicker just to run the query, but I can't reproduce that so far on a vanilla test system. I also don't know how SQL server decides it would be quicker just to run the query. In any case, for newly inserted data, you can see from the Explain Plan that the estimated number of rows is tiny (maybe 1) where it will in fact be hundreds of thousands.

I'm essentially thinking about developing an alternative version of sp_updatestats, because the existing one is taking too long to run (more than an hour on some systems).

My thinking is that there are two types of statistics;

1. Stats on non-primary key columns. The histogram distributions in these statistics don't need to be recalculated very much, since the nature of the added data is relatively uniform over time. However, the stats don't store % distributions, they store numbers of rows for bins. I'd like to just incrementally multiply these numbers, but I don't think I can manipulate the statistics data?

2. Stats on the incrementing primary key columns. These don't necessarily need to be recalculated very much either. As well as the incremental bin adjustment described above, I'd just need to correct the max value.

Before I waffle on too much, any thoughts on the above?

I've read about trace flags (2389, 2390?) which can be set when starting SQL server which seem related to this, but I'm not so keen on using trace flags on a production system