Why do statistics get out of date so quickly in SQL Server?

Question

We have a somewhat complex SQL update query which gets run a few times a month. Most of the time it seems to run really fast, but on some databases, it takes a really long time. After running an "UPDATE STATISTICS" on the tables involved, the update immediately runs quickly again. We finally set up a nightly task which calls UPDATE STATISTICS on all the tables in the database. But that didn't seem to fix the problem. We're still ending up having to run "UPDATE STATISTICS" manually each time. Why would statistics go stale so quickly?

Here's approximately what the query looks like:

UPDATE DataTableA
SET DataTableA.IndexedColumn1 = 123456789, DataTableA.Flag1 = 1
FROM DataTableA WITH (INDEX(IX_DataTableA))
INNER JOIN GroupingTableA ON GroupingTableA.ForeignKey1 = GroupingTableA.PrimaryKey
INNER JOIN LookupTableA ON DataTableA.ForeignKey3 = LookupTableA.PrimaryKey
LEFT OUTER JOIN GroupingTableB ON DataTableA.IndexedColumn2 = GroupingTableB.IndexedColumn2
WHERE GroupingTableB.IndexedColumn1 = 123456789
AND DataTableA.IndexedColumn1 IS NULL
AND DataTableA.IndexedColumn2 IN ( ... 300 entries here ... )
AND DataTableA.Deleted = 0
AND GroupingTableA.Date <= GroupingTableB.EndDate
AND GroupingTableA.Date >= DATEADD(month, -1, GroupingTableB.StartDate)
AND LookupTableA.Column2 = 1
AND DataTableA.Status1 IN (1, 3)
AND DataTableA.Status2 NOT IN (1, 3, 9)

DataTableA contains millions of rows.
GroupingTableA and GroupingTableB each contain tens of thousands of rows.
LookupTableA contains dozens of rows.
Index IX_DataTableA is an index on (IndexedColumn1 ASC, IndexedColumn2 ASC)

Answer 1

I don't think your statistics will matter as much if you are forcing it to use a specific index (WITH (INDEX(IX_DataTableA))

Are you sure you know better than the optimizer?

I would start by looking at the execution plan for a fast update and a slow update. Also, are the number of records being updated comparable for fast/slow queries?

Answer 2

When the auto stats are on, the engine would like to update the stats quite often, usually way more often than nightly:

 Table Type | Empty Condition | Threshold When Empty |Threshold When Not Empty 
_________________________________________________________________________________
 Permanent  | < 500 rows      | # of Changes >= 500  | # of Changes >= 500 + (20% of Cardinality)
___________________________________________________________________________
 Temporary  | < 6 rows        | # of Changes >= 6    | # of Changes >= 500 + (20% of Cardinality)

But why not take out the guess and simply deploy a plan guide? See Understanding Plan Guides.