just to quibble with words a little bit: sp_updatestats only checks to see if rowmodctr is 0 or not. If it is not 0, it updates stats. That's a little different from only updating if the statistics need it. It is also worth considering that the rowmodctr that it checks was the actual indicator in SQL 2000, but is only an approximation in SQL 2005. The colmodctr that is actually used for autoupdate of statistics in 2005 is not visible to us.

Despite what a whitepaper may say, I've seen in dbcc show_statistics times when on very large indexes (Very large, as in > 1 billion rows) that the sampling used was less than 1 percent. That's probably a sufficient sampling most times, but can lead to skewed statistics on the very rare occasion. The times where I have seen skewed statistics, there was no mistaking what it did to performance. In cases like these, if statistics are rebuilt occasionally using a higher sampling, then the threshold of modifications that leads to an autoupdate of statistics is never reached, so the statistics don't get updated with the smaller smapling that the autoupdate stats uses.

One of the biggest reasons I can think of to update statistics periodically (nightly if you have slow time during the night) is that the modctr is set back to 0 when you update, and this makes it unlikely that the number of modifications during peak business will cross the threshold that trips autoupdate of stats on a table. The autoupdate should be used like a safety valve in case that threshold is reached, but minimizing autoupdate during your busiest time (by proactively updating during slow time) is a good practice.