Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

SQL Server: every stream lookup by name does a key lookup to get `Version`

Aperta
  • Qualcosa non va?
#617 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 7 giorni

@nmummau ci sta già lavorando.

Dal 14/10/2026.

  • #621 di @nmummau — aperta

Valutazione

Difficoltà
3/5
Tempo stimato
1-2 giorni
Idoneità per principianti
25/100
Tipo di issue
Refactoring
Chiarezza
Specificata chiaramente
Stato di attività
Ferma
Stack tecnologico
csharp, sql
Ambito
backend, databases

Direzione di ricerca

Start with the schema in src/SqlServer/src/Eventuous.SqlServer/Scripts/1_Schema.sql, where the Streams table and UQ_StreamName are defined, and with check_stream, append_events and the CATCH block that matches the constraint name. Pull request #621 is already open against this issue, so check it before starting. Done means the lookup by name becomes a covered index seek, existing databases get an upgrade step, and the concurrency tests from #598 still pass.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

Summary

check_stream, read_stream_forwards, and read_stream_backwards all start with the same query:

SELECT
    @current_version = [Version],
    @stream_id = StreamId
FROM __schema__.Streams
WHERE StreamName = @stream_name;

The only index on StreamName is the unique constraint UQ_StreamName. It holds StreamName and the clustered key StreamId, but not Version. So SQL Server seeks UQ_StreamName and then does a key lookup into PK_Streams to get Version. This occurs once for every append and every stream read.

How I found it

We capture the actual execution plans from our integration tests and analyze them with Performance Studio. It reports this statement as a critical "Key Lookup":

Key Lookup — SQL Server found rows via a nonclustered index but had to go back to the clustered index for additional columns. Columns fetched: Streams.Version

Statement in the plan (from check_stream, called by append_events):

SELECT @current_version = [Version], @stream_id = StreamId
FROM eventuous.Streams
WHERE StreamName = N'InventoryNodeStock-102'

Environment: Eventuous 0.17.0, SQL Server 2022. The schema in src/SqlServer/src/Eventuous.SqlServer/Scripts/1_Schema.sql on dev has the same index, so the plan shape is the same there.

Suggested change

Replace the unique constraint with a unique index that has the same name and includes Version:

CREATE TABLE __schema__.Streams
(
    StreamId   INT IDENTITY (1,1) NOT NULL,
    StreamName NVARCHAR(850)      NOT NULL,
    [Version]  INT DEFAULT (-1)   NOT NULL,
    CONSTRAINT PK_Streams PRIMARY KEY CLUSTERED (StreamId) WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON),
    CONSTRAINT CK_VersionGteNegativeOne CHECK ([Version] >= -1),
    INDEX UQ_StreamName UNIQUE NONCLUSTERED (StreamName) INCLUDE ([Version])
);

A UNIQUE constraint cannot have INCLUDE columns, so it must become a unique index. Keep the name UQ_StreamName: the CATCH block in check_stream looks for that name in the error message, and already handles both error 2627 (constraint) and 2601 (unique index).

Trade-offs

  • Benefit: The lookup by name becomes one index seek. This saves a few logical reads on each append and each stream read, and the read path touches one index instead of two.
  • Cost: The UPDATE ... SET [Version] in append_events must now also write the nonclustered index. Version is a 4-byte INT, so the index stays narrow, but it is one more index write for each append.
  • Locking: check_stream takes UPDLOCK on the row it reads. With a covering index, that read locks only the UQ_StreamName key, not the clustered row. Two concurrent appends to the same stream still meet on the same UQ_StreamName key, so they must still be serialized, but the concurrency tests added in #598 should confirm this.
  • Existing databases: 1_Schema.sql creates the table only IF OBJECT_ID(...) IS NULL, so existing databases do not get the change. The change needs an upgrade step (drop the constraint, create the index), or a note in the release notes.

The gain for each call is small. But this query runs on the hot path of every append and every aggregate load, so I think the change is worth it. I can open a PR if you agree with the approach.

Lingua principale
C#
Stelle
512
Fork
99
Merge medio
5g 6h
PR unite (30g)
5

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di Eventuous/eventuous

Tutte le issue di Eventuous/eventuous

Issue simili

Altre issue su C#

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.