[ENHANCEMENT (P3-Nice-to-have)]: Temporal history table statistics: history tables are discovered and scheduled separately from their parent, but parent-history stat coherence is not guaranteed

[nanoDBA]

Summary

System-versioned temporal tables have a current table and a history table. Statistics on these two tables are independently scheduled by sp_StatUpdate based on their individual modification counters. If history table statistics are not updated in the same run as the current table, cross-table cardinality estimates for temporal queries spanning both tables will be inconsistent.

Current Behavior

sp_StatUpdate tracks temporal type via @current_temporal_type (values: 1=history, 2=current/WITH_SYSTEM_VERSIONING). Both the current table and history table are discovered independently as separate objects with their own statistics, modification counters, and priority scores. In multi-table maintenance windows, the history table may be skipped (below modification threshold, below page count threshold, or excluded by @Tables filter) while the current table is updated.

Temporal queries using FOR SYSTEM_TIME join the current and history tables. The optimizer uses statistics from both tables to build the plan. Stale history statistics + fresh current statistics = inconsistent cross-table cardinality.

Expected Behavior / Proposed Fix

When a current temporal table's statistics are updated, detect and co-schedule history table statistics in the same pass:

1. During discovery, link current table statistics to their history table via sys.tables relationship (history_table_id column).
2. When a current table's statistics meet the update threshold, also include the history table's statistics in the candidate set for the same run — regardless of the history table's own modification counter.

Add TEMPORAL_HISTORY_COSCHEDULED tag to CommandLog ExtendedInfo for co-scheduled history stats.

Add a @TemporalCoSchedule bit = 1 parameter (default on) to control this behavior.

Technical Detail

sys.tables.history_table_id links current temporal tables to their history. A query SELECT * FROM dbo.Orders FOR SYSTEM_TIME ALL reads from both dbo.Orders (current) and dbo.OrdersHistory (history). If dbo.Orders statistics are updated (accurate row count, key distribution) but dbo.OrdersHistory statistics are stale (missing months of historical rows), the optimizer produces wrong cardinality estimates for the history scan portion of the plan. Co-scheduling ensures temporal query plans use coherent statistics from both partitions.

Test Cases

• Create temporal table with 1M current rows and 5M history rows. Update current table only. Run sp_StatUpdate. Verify history table statistics are also updated in the same pass.
• Set @TemporalCoSchedule=0. Verify history table is NOT co-scheduled (reverts to independent discovery).
• Verify TEMPORAL_HISTORY_COSCHEDULED appears in CommandLog ExtendedInfo for co-scheduled history stats.

References

• sys.tables — temporal_type (1=history, 2=current), history_table_id
• SQL Server docs: System-Versioned Temporal Tables — statistics behavior
• FOR SYSTEM_TIME query syntax and optimizer plan generation

[nanoDBA]

Fixed in v2.19 (611877b). New @TemporalCoSchedule parameter (default Y). When system-versioned temporal tables qualify, co-schedules history table stats via sys.tables.history_table_id. Prevents cross-table cardinality inconsistency for FOR SYSTEM_TIME queries.