Why Changing a Date Turns a SQL Server Query from 5 seconds to Stalled – Fixing Statistics

Problem Description

In SQL Server 2008 R2 a query joining ODS_TABLE_A (≈7 M rows per day, >60 B total, indexed and partitioned) and MID_TABLE_B (≈200 k rows per day, 30 M total, indexed) runs in about five seconds when etldate ≤ 2016-08-12, but does not return when the date is ≥ 2016-08-16.

The original query uses multiple sub‑queries, joins, and conditional aggregates:

select
COUNT(distinct(case when COL_USERID3 is null then COL_USERID6 end)) as 'aa',
COUNT(distinct(case when COL_USERID3 is null and COL_USERID7 is not null then COL_USERID6 end)) as 'bb',
COUNT(distinct(case when COL_USERID3 is not null then COL_USERID6 end)) as 'cc',
COUNT(distinct(case when COL_USERID3 is not null and COL_USERID7 is not null then COL_USERID6 end)) as 'dd',
SUM(case when COL_USERID3 IS not null then ee end) as 'ee'
from (
    select c.COL_USERID3,c.ee,g.COL_USERID6
    from (
        select  b.COL_USERID2 as COL_USERID3,COUNT(b.COL_USERID2) as ee
        from (
            select COL_USERID as COL_USERID1,min(EventTime) as time1
            from ODS_TABLE_A
            where  EtlDate = '2016-08-12'
            and colid LIKE 'heihei%'
            group by COL_USERID
        ) as a
        join (
            select COL_USERID as COL_USERID2,eventtime as time2
            from ODS_TABLE_A
            where EtlDate = '2016-08-12'
            and ItemId = '1111111111101'
            and colid like 'haha-%'
            and colid not like 'haha-skill%'
            and colid not like 'haha-fine%'
        ) as b on a.COL_USERID1 = b.COL_USERID2 and  a.time1 > b.time2
        group by b.COL_USERID2
    ) as c
    right join (
        select  DISTINCT d.COL_USERID4 as COL_USERID6
        from (
            select distinct COL_USERID as COL_USERID4
            from MID_TABLE_B
            where etldate = '2016-08-12'
        ) as d
        join (
            select COL_USERID AS COL_USERID5
            from ODS_TABLE_A
            where  EtlDate = '2016-08-12'
            and colid LIKE 'heihei%'
        ) as f on d.COL_USERID4 = f.COL_USERID5
    ) as g on c.COL_USERID3 = g.COL_USERID6
) as i
left join (
    select COL_USERID as COL_USERID7
    from MID_TABLE_B
    where EtlDate = '2016-08-12'
    and IsTodayPay = '1'
) as h on i.COL_USERID6 = h.COL_USERID7

Solution Process

1. The execution plan for the original query used the expected indexes, indicating no missing index.

2. Changing the filter to etldate='2016-08-16' caused the optimizer to generate a plan that ran for over half an hour without returning results. The estimated plan looked similar, but the actual execution lost parallelism.

3. Adding TOP 1 reduced the data set and confirmed that the parallelism degree disappeared in the new plan.

4. Forcing parallelism with OPTION (QUERYTRACEON 8649) had no effect. The underlying cause was identified as stale statistics for the large table.

5. Updating the statistics for ODS_TABLE_A resolved the issue: UPDATE STATISTICS ODS_TABLE_A; SQL Server updates statistics automatically, but for very large tables the automatic threshold may be insufficient after bulk inserts. Running UPDATE STATISTICS (or sp_updatestats) forces a fresh distribution, allowing the optimizer to choose a parallel plan again.

Reference: Microsoft Docs – UPDATE STATISTICS (https://msdn.microsoft.com/zh-cn/library/ms187348.aspx)

Conclusion

The performance discrepancy was caused by newly inserted rows in the large table lacking up‑to‑date statistics. When the date filter moved past those rows, the optimizer misestimated row counts, selected a non‑parallel plan, and the query stalled. Regularly updating statistics or configuring auto‑update thresholds prevents this problem.