Plan değişimi P422

(@MaxAttempts int,@BatchSize int,@LeaseSeconds int)WITH c AS (
        SELECT TOP (@BatchSize) *
        FROM rag.EmbedQueue WITH (READPAST, UPDLOCK, ROWLOCK)
        WHERE Status = 'Queued' OR (Status = 'Running' AND LeaseUntilUtc < SYSUTCDATETIME() AND Attempt < @MaxAttempts)
        -- Vaka/KB önce: bekçi her gün binlerce profili kuyruğa alır, onaydan sonra gelen vaka onların ardında beklemesin
        ORDER BY CASE SourceType WHEN 'Profile' THEN 1 ELSE 0 END, QueueID
    )
    UPDATE c SET Status = 'Running', Attempt = Attempt + 1, LeaseUntilUtc = DATEADD(second, @LeaseSeconds, SYSUTCDATETIME())
    OUTPUT inserted.QueueID, inserted.SourceType, inserted.SourceRef, inserted.Attempt

Plan change case #P422 | DB: DB-11 | Regression | Confidence: Medium (review pending)
Query: (@MaxAttempts int,@BatchSize int,@LeaseSeconds int)WITH c AS ( SELECT TOP (@BatchSize) * FROM rag.EmbedQueue WITH (READPAST, UPDLOCK, ROWLOCK) WHERE Status = ? OR (Status = ? AND LeaseUntilUtc < SYSUTCDATETIME() AND Attempt < @MaxAttempts) ORDER BY CASE SourceType WHEN ? THEN ? ELSE ? END, QueueID ) UPDATE c SET Status = ?, Attempt = Attempt + ?, LeaseUntilUtc = DATEADD(second, @LeaseSeconds, SYSUTCDATETIME()) OUTPUT inserted.QueueID, inserted.SourceType, inserted.SourceRef, inserted.Attempt
Change: 2026-10-02 17:00 UTC, plan 35406 → 35408, pattern: Transition
Impact (Query Store, per execution): duration 0.4 → 16.9 ms (+3964%); CPU 0.4 → 16.9 ms (+3955%); reads 13 → 683 (+5155%); rows 2 → 4.17 (+109%)
Plan difference: Access only in the old plan: rag.EmbedQueue Table Update; rag.EmbedQueue Table Scan; Access only in the new plan: rag.EmbedQueue Index Update [UX_EmbedQueue_Pending]; rag.EmbedQueue Clustered Index Update [PK_EmbedQueue]; rag.EmbedQueue Clustered Index Scan [PK_EmbedQueue]; Warning only in the new plan: UnmatchedIndexes; Estimated cost: 0.025 → 0.62; estimated rows: 2 → 100
Candidate cause: STATS — Statistic rag.EmbedQueue.PK_EmbedQueue was updated 2026-10-01 21:13 (sampled 100%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.EmbedQueue.IX_EmbedQueue_Status was updated 2026-10-02 16:27 (sampled 100%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.EmbedQueue.UX_EmbedQueue_Pending was updated 2026-10-02 16:25 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.EmbedQueue._WA_Sys_00000005_50D1B250 was updated 2026-10-02 16:25 (sampled 100%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.EmbedQueue._WA_Sys_00000006_50D1B250 was updated 2026-10-02 16:27 (sampled 100%); 5 updates in the window; the plans use this statistic.
Candidate cause: WORKLOAD — The plans see a different workload: rows per execution 2 → 4.17. The plans may have been compiled for different parameters or batch sizes.
Counter-evidence: LOW_SAMPLE — The old plan ran only 30 times: the sample is small and the average is weak.
Counter-evidence: METADATA_GAP — Metadata collection started on 2026-10-01 and the change was on 2026-10-02: index, statistics and module events could not be evaluated (no data, not no events).
Confidence rationale: Medium: more than one candidate (STATS, WORKLOAD) — competing explanations. Weak counter-evidence: LOW_SAMPLE, METADATA_GAP.
Note: this is a candidate cause, not proof of the root cause.