AuditLog: default Audit Log page + keyset paging scan the whole table; flip clustered key to (OccurredAtUtc, EventId) (B2) #111

New Issue

2026-11-09T19:11:24-05:00

dohertj2 commented

2026-11-09 19:11:24 -05:00

From the ScadaBridge#78 benchmark (docs/plans/2026-11-09-auditlog-clustered-key-benchmark.md, main): with today's clustered key (EventId, OccurredAtUtc) and random-GUID EventIds, the default Audit Log page (newest 50) and keyset paging read the whole dbo.AuditLog table — IX_AuditLog_OccurredAtUtc can't serve ORDER BY OccurredAtUtc DESC, EventId DESC on the partitioned table. At 3M rows: 739k logical reads / 2.3 s for page 1 vs 51 reads / 15 ms with the key flipped. Cost grows linearly with volume. An extra (OccurredAtUtc DESC, EventId DESC) non-clustered index did not fix it.

Recommended change (variant B2): clustered (OccurredAtUtc, EventId) + an aligned unique (EventId, OccurredAtUtc) index (keeps the idempotency probe and EventId uniqueness). Ingest unchanged (~4k rows/s, still fragmented); reads 100–1000× better on page/keyset/site-week/actor/hash-chain-select shapes; chain stamp/KPI/purge unchanged.

Needs:

Decide to proceed (owner).
EF migration: rebuilds the clustered index and all 12 non-clustered ones — needs a planned write window (online rebuild not guaranteed on Standard). Also update the partition-switch staging table, re-check role grants and the hash-chain code.
Measure the real DEV/QA table size first to size the window (blocked like #70 on a SQL login that can read the ScadaBridge DB).
Mixed-version: old and new binaries work on either shape; rollback = reverse rebuild.

From the ScadaBridge#78 benchmark (`docs/plans/2026-11-09-auditlog-clustered-key-benchmark.md`, main): with today's clustered key `(EventId, OccurredAtUtc)` and random-GUID EventIds, **the default Audit Log page (newest 50) and keyset paging read the whole `dbo.AuditLog` table** — `IX_AuditLog_OccurredAtUtc` can't serve `ORDER BY OccurredAtUtc DESC, EventId DESC` on the partitioned table. At 3M rows: 739k logical reads / 2.3 s for page 1 vs 51 reads / 15 ms with the key flipped. Cost grows linearly with volume. An extra `(OccurredAtUtc DESC, EventId DESC)` non-clustered index did not fix it. Recommended change (variant B2): clustered `(OccurredAtUtc, EventId)` + an aligned unique `(EventId, OccurredAtUtc)` index (keeps the idempotency probe and EventId uniqueness). Ingest unchanged (~4k rows/s, still fragmented); reads 100–1000× better on page/keyset/site-week/actor/hash-chain-select shapes; chain stamp/KPI/purge unchanged. Needs: - [ ] Decide to proceed (owner). - [ ] EF migration: rebuilds the clustered index and all 12 non-clustered ones — needs a planned write window (online rebuild not guaranteed on Standard). Also update the partition-switch staging table, re-check role grants and the hash-chain code. - [ ] Measure the real DEV/QA table size first to size the window (blocked like #70 on a SQL login that can read the ScadaBridge DB). - [ ] Mixed-version: old and new binaries work on either shape; rollback = reverse rebuild.

dohertj2 added the type:debt priority:p2 labels 2026-11-09 19:11:24 -05:00

dohertj2 referenced this issue

2026-11-09 19:11:30 -05:00

Benchmark an AuditLog clustered-key flip and revisit per-node KPI sampling cadence #78

dohertj2 commented

2026-11-09 22:13:25 -05:00

Owner decision 2026-11-09: proceed with B2 — clustered (OccurredAtUtc, EventId) + aligned unique (EventId, OccurredAtUtc). Build the EF migration now; ship it in the next coordinated central-pair migration window with #107 / #102 item 1 / #50, after sizing the DEV/QA table (needs the read login from #70).

**Owner decision 2026-11-09:** **proceed with B2** — clustered (OccurredAtUtc, EventId) + aligned unique (EventId, OccurredAtUtc). Build the EF migration now; ship it in the next coordinated central-pair migration window with #107 / #102 item 1 / #50, after sizing the DEV/QA table (needs the read login from #70).

dohertj2 commented

2026-11-09 23:33:23 -05:00

B2 migration implemented — 5e7d5389 (main). Kept open for the DEV/QA sizing + write window (needs the read login, like #70).

Migration 20261110033732_FlipAuditLogClusteredKey (sorts after #62's AddConfigGraphRowVersions): drops the 12 NC indexes, re-adds PK_AuditLog as clustered (OccurredAtUtc, EventId) on ps_AuditLog_Month, adds aligned unique UX_AuditLog_EventId_Occurred (EventId, OccurredAtUtc), rebuilds the 12 NC indexes unchanged (all on the partition scheme). Up is guarded/re-runnable; Down is the inverse rebuild. Production scripts: docs/plans/sql/FlipAuditLogClusteredKey.sql + .rollback.sql (QUOTED_IDENTIFIER header; both run clean via sqlcmd on a local container).
Partition-switch staging table now reads the live clustered key order from sys.index_columns and builds its PK to match, so new binaries purge either shape (before/after the flip, after a Down).
Correction to the mixed-version note: old binaries do NOT work on the flipped shape for one statement — their staging table hard-codes (EventId, OccurredAtUtc), so their monthly switch-out fails (SWITCH … no identical index, rolled back, nothing purged, retried next tick). Reads/ingest/chain are fine. So: ship with the binaries, both centrals stopped. Added to the #107 deploy-gate list.
Grants unchanged after Up and Down (table + column-level UPDATE (ChainSeq, RowHash)); hash-chain code needs no change (UPDATE join on (EventId, OccurredAtUtc)); all hash-chain, archive and purge MSSQL tests pass on the new shape.

Evidence (local SQL Server 2022 container, never a wonder DB):

Reduced benchmark, 300k rows / 631 MB, same query shapes as the doc: page 1 90,532 → 34 logical reads (plan Top over Clustered Index Scan … ORDERED BACKWARD, no sort), keyset page (cursor in Sept) 90,532 → 46, idempotency probe 14 → 14. Rebuild of 631 MB took ~9 s on an idle container — real window must be sized from DEV/QA (SELECT SUM(used_page_count)*8/1024 FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.AuditLog')), log needs ~2× table size.
FlipAuditLogClusteredKeyMigrationTests (MSSQL): index shapes + grants after Up; Down/Up round trip, re-run of Up's SQL is a no-op, switch-out purge on both shapes; repository page-1/keyset reads at 20k rows: 76 vs 7,420 and 274 vs 7,393 (asserted ≥10×). Mutation: hard-coding the old staging key fails the new-shape purge with the SWITCH error.
dotnet build 0 warnings; ConfigurationDatabase.Tests 508/508, AuditLog.Tests 446/446.

**B2 migration implemented — `5e7d5389` (main).** Kept open for the DEV/QA sizing + write window (needs the read login, like #70). - Migration `20261110033732_FlipAuditLogClusteredKey` (sorts after #62's `AddConfigGraphRowVersions`): drops the 12 NC indexes, re-adds `PK_AuditLog` as clustered `(OccurredAtUtc, EventId)` on `ps_AuditLog_Month`, adds aligned unique `UX_AuditLog_EventId_Occurred (EventId, OccurredAtUtc)`, rebuilds the 12 NC indexes unchanged (all on the partition scheme). Up is guarded/re-runnable; Down is the inverse rebuild. Production scripts: `docs/plans/sql/FlipAuditLogClusteredKey.sql` + `.rollback.sql` (QUOTED_IDENTIFIER header; both run clean via `sqlcmd` on a local container). - Partition-switch staging table now reads the live clustered key order from `sys.index_columns` and builds its PK to match, so new binaries purge either shape (before/after the flip, after a Down). - **Correction to the mixed-version note:** old binaries do NOT work on the flipped shape for one statement — their staging table hard-codes `(EventId, OccurredAtUtc)`, so their monthly switch-out fails (`SWITCH … no identical index`, rolled back, nothing purged, retried next tick). Reads/ingest/chain are fine. So: ship with the binaries, both centrals stopped. Added to the #107 deploy-gate list. - Grants unchanged after Up and Down (table + column-level `UPDATE (ChainSeq, RowHash)`); hash-chain code needs no change (UPDATE join on `(EventId, OccurredAtUtc)`); all hash-chain, archive and purge MSSQL tests pass on the new shape. Evidence (local SQL Server 2022 container, never a wonder DB): - Reduced benchmark, 300k rows / 631 MB, same query shapes as the doc: page 1 **90,532 → 34** logical reads (plan `Top` over `Clustered Index Scan … ORDERED BACKWARD`, no sort), keyset page (cursor in Sept) **90,532 → 46**, idempotency probe 14 → 14. Rebuild of 631 MB took ~9 s on an idle container — real window must be sized from DEV/QA (`SELECT SUM(used_page_count)*8/1024 FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.AuditLog')`), log needs ~2× table size. - `FlipAuditLogClusteredKeyMigrationTests` (MSSQL): index shapes + grants after Up; Down/Up round trip, re-run of Up's SQL is a no-op, switch-out purge on both shapes; repository page-1/keyset reads at 20k rows: 76 vs 7,420 and 274 vs 7,393 (asserted ≥10×). Mutation: hard-coding the old staging key fails the new-shape purge with the SWITCH error. - `dotnet build` 0 warnings; ConfigurationDatabase.Tests 508/508, AuditLog.Tests 446/446.

dohertj2 referenced this issue

2026-11-09 23:33:24 -05:00

Audit hash chain: signing/anchoring decision, enablement plan, chainer login, migration deploy gate (#41) #107

dohertj2 referenced this issue

2026-11-09 23:48:19 -05:00

Audit hash chain: signing/anchoring decision, enablement plan, chainer login, migration deploy gate (#41) #107

dohertj2 added the blocked label 2026-11-10 08:43:37 -05:00

Sign in to join this conversation.