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

SQL Server: stream reads do one key lookup per event, and `IDX_EventsStream` duplicates `UQ_StreamIdAndStreamPosition`

Aperta
  • Qualcosa non va?
#618 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
18/100
Tipo di issue
Refactoring
Chiarezza
Abbastanza chiara
Stato di attività
Ferma
Stack tecnologico
csharp, sql
Ambito
databases

Direzione di ricerca

The stream read query and both indexes are defined in src/SqlServer/src/Eventuous.SqlServer/Scripts/1_Schema.sql, so start there and confirm that nothing references IDX_EventsStream. Removing that index is the low-risk part, and the open pull request #621 may already cover it. Done means the schema change is in place, the SQL Server tests still pass, and the key-lookup trade-off has a maintainer decision.

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

Descrizione

Summary

read_stream_forwards and read_stream_backwards read a stream with this query:

SELECT TOP (@count)
    MessageId, MessageType, StreamPosition, GlobalPosition,
    JsonData, JsonMetadata, Created
FROM __schema__.Messages
WHERE StreamId = @stream_id
AND StreamPosition >= @from_position
ORDER BY StreamPosition;

SQL Server seeks UQ_StreamIdAndStreamPosition (StreamId, StreamPosition) and then does one key lookup into the clustered index PK_Events (GlobalPosition) for each event to get MessageId, MessageType, JsonData, JsonMetadata, and Created. When an aggregate with N events loads, the read does N random seeks into the clustered index.

Also, IDX_EventsStream (StreamId) has the same leading column as UQ_StreamIdAndStreamPosition (StreamId, StreamPosition). Every query that can use IDX_EventsStream can also use the unique index, so IDX_EventsStream only adds a write and storage for each appended event.

How I found it

We capture the actual execution plans from our integration tests and analyze them with Performance Studio. It reports the stream read as a critical "Key Lookup", for both a full read (TOP (2147483647), from read_stream_forwards) and a single-event read (TOP (1)):

Key Lookup — SQL Server found rows via a nonclustered index but had to go back to the clustered index for additional columns. Columns fetched: Messages.MessageId, Messages.MessageType, Messages.JsonData, Messages.JsonMetadata, Messages.Created

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 indexes.

Suggested changes

1. Remove IDX_EventsStream (low risk)

UQ_StreamIdAndStreamPosition already starts with StreamId. It supports the foreign key FK_MessageStreamId and every seek on StreamId. I did not find a query or index hint in the scripts that names IDX_EventsStream. Removing it makes each append cheaper and has no effect on reads.

2. Decide on the key lookup (design question)

I do not think that there is a free fix here, so I am asking which trade-off you prefer:

Option Stream read (aggregate load) read_all_forwards / subscriptions Append Storage
Keep as is Seek + one key lookup per event Clustered range scan, no lookups Sequential insert at the end of the clustered index Baseline
INCLUDE all other columns in UQ_StreamIdAndStreamPosition One range seek, no lookups No change Writes the JSON a second time About double, because JsonData and JsonMetadata are stored twice
Cluster on (StreamId, StreamPosition), make GlobalPosition a nonclustered primary key One range seek, no lookups Seek + one key lookup per event Inserts go to many places in the clustered index (page splits) About the same

The INCLUDE option is probably not acceptable because of the storage cost. Changing the clustered key moves the lookups from stream reads to global reads, so the right choice depends on which read path is hotter. For a store that loads aggregates much more often than it runs catch-up subscriptions, the clustered key on (StreamId, StreamPosition) can be the better choice.

If you prefer to keep the current design, a short note in the SQL Server docs would help: it would tell users that the key lookups are expected, and that snapshots or short streams keep their cost low.

I can open a PR for change 1 now, and for change 2 after we agree on a direction.

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.