Why are statistics getting out of date quickly in SQL Server?

Question

We have a somewhat complicated SQL update request that runs several times a month. In most cases this works very fast, but in some databases it takes a very long time. After starting UPDATE STATISTICS in the corresponding tables, the update immediately quickly starts quickly. Finally, we created a night task that calls UPDATE STATISTICS on all tables in the database. But this did not help solve the problem. We still have to run the “STATISTICS UPDATE” manually each time. Why are statistics so outdated so quickly?

Here's what the request 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 contain tens of thousands of rows.
LookupTableA contains dozens of rows.
Index IX_DataTableA is the index on (IndexedColumn1 ASC, IndexedColumn2 ASC)

Answer 1

I don’t think your statistics will make much difference if you force it to use a specific index ( WITH (INDEX(IX_DataTableA))

Are you sure you know better than the optimizer?

I would start by reviewing the execution plan for quick updates and slow updates. Also, is the number of updated records comparable for fast / slow queries?

Answer 2

When auto statistics are turned on, the engine would like to frequently update statistics, usually more often than at night:

  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 make a guess and just roll out a plan? See Understanding Plan Guides .