Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 2 down vote accepted

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

share|improve this answer
    
Ingenious and creative. Thanks for the suggestions. – 孔夫子 Nov 4 '12 at 23:55

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.