Sql-server – TimeStamp Column Auto Update Upon Insert and Modification

Some info, if not a definitive answer

It's been blogged recently

• When are Statistics Updated – What triggers Statistics to Update
• Comment from Paul Randal

There is a whitepaper too. See the section "Maintaining Statistics in SQL Server 2008" where there are some conditions that sound like affect you. Example:

One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.

At the end there are some settings to check too: what if OFF at the DB level which overrides an ON at the index/stat level?

HTH...

Bad news. You said ETL process - that means heavy data loads, and inserts/updates/deletes have a single disk bottleneck: the transaction log file. You can add multiple transaction log files, but they're used sequentially, not load-balanced (except in some really edge-case scenarios.)

Instead, what you need is multiple databases. In data warehouses, you typically use a different source database than a destination database, like a staging database. That way, you can put its databases on different drives (and even different classes of drives). For example, some staging databases live on cheap local SSDs.

If you do find yourself storage-bottlenecked on data file reads or writes, you're better off solving that problem by adding memory (to cache the data and avoid reads altogether) or by striping the data across more drives, not less. By splitting the data files into smaller pieces, and giving them each fewer drives, you're usually making the problem worse.