[Order 06][P2] Replace offset checkpoint paging with keyset paging

[AGiorgetti]

Priority order

Order: 06 of 09
Priority: P2 performance

Problem

Checkpoint reads use offset/ROW_NUMBER paging instead of advancing by the last seen checkpoint.

Evidence:

• src/NEventStore.Persistence.Sql/SqlPersistenceEngine.cs:347
• Checkpoint reads pass a fixed checkpoint token.
• Common SQL uses LIMIT @Limit OFFSET @Skip.
• SQL Server transforms checkpoint reads to ROW_NUMBER() paging in MsSqlDialect.CommonTableExpressionPaging.

Impact

• Long catch-up reads become increasingly expensive as @Skip grows.
• SQL Server recomputes row numbers for every page.
• This is a hot path for polling clients and projections.

Mandatory test-first workflow

• Add tests first that verify ordered, complete multi-page checkpoint reads.
• Tests must cover result counts greater than the configured page size.
• Tests must cover from/to checkpoint ranges.
• Do not change production paging logic until these tests are in place.

Proposed action

• Page checkpoint reads by last seen CheckpointNumber instead of offset.
• Update sync and async paging delegates for checkpoint queries to set @CheckpointNumber to the last row.
• Keep @ToCheckpointNumber as the upper bound for range reads.

Acceptance criteria

• Multi-page checkpoint reads remain ordered and gap-free.
• Bucket-scoped and global checkpoint reads are both covered.
• A benchmark or integration timing plan is documented for large catch-up reads.

Source: docs/Project-Analysis-Issue-Drafts.md.