What is a good guideline for sampling for statistics updates

Question

Scope: A billion record table that is indexed in 10 ways. Rate of change per day is 1/2 a percent.

Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most likely hit the tail end. I assume statistics need updating frequently for this type, otherwise the recent data becomes unrepresented in the index?

Other indexes are more randomly distributed, e.g. (Customer Key, Datetime). These can do with less frequent updates, since the statistics are quite representative of the whole. We can let the index change enough to force auto statistics updates on these, correct?

For both types, is there any benefit from increasing the sampling from 10% to 100%, if the data to be sampled is random and quite representative of the whole?

Looking for best practice with TB data.

Answer 1

If you always query the tail, you can give a shot to filtered indexes. Index 10 ways with, say, WHERE datetimecolumn > @INDEXBUILDDAY-30. Periodically (daily, weekly?) you can move this filter forward by adding (online) a new set of filterd indexes and then dropping the old ones. With a much smaller size and a much more recent build (implying recent stats), they would be 'fresh' and very appetizing for the optimizer. The periodic rebuild doesn't even need to scan the base table (no huge IO, no BP cache pollution), the old filtered index should always be usable as a source to build the next filtered index (it covers it).

For those queries that land outside the covered filtered tail, you can keep some (less than the 10 ways hopefully) full, unfiltered indexes. With the right voodoo invocation the optimizer might get your point and use the filtered ones when appropriate and only resort to the full ones when necessary...

Answer 2

There are a couple of trace flags that can help with statistics on ascending keys, TF2389 and TF2390.

Since 2005SP1, SQL Server has identified and "branded" ascending keys. If TF2389 is set and the leading column of a covering index is branded, the statistics for the column will be updated automatically by identifying the highest key value and updating the existing histogram. Enabling TF2930 causes the same update to occur where a column hasn't been identified (branded) as ascending.

Keep in mind that the statistics update occurs during query compilation, so if recompilation is rare you may want to test a RECOMPILE hint on some procedures or queries to force an update. Alternatively, a periodic sp_recompile on the procedure may help if the cost of compilation is high.

Statistics on Ascending Columns is a great article that covers the mechanics.

NB: Usual caveats to using trace flags apply... test, test and test some more.