Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

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

Abierto
  • ¿Algo no está bien?
#618 1 comentario 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 7 días

@nmummau ya está trabajando en esto.

Desde el 14/10/2026.

  • #621 de @nmummau — abierto

Evaluación

Dificultad
3/5
Tiempo estimado
1-2 días
Aptitud para principiantes
18/100
Tipo de issue
Refactorización
Claridad
Bastante claro
Estado de actividad
Estancado
Stack tecnológico
csharp, sql
Área
databases

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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.

Lenguaje dominante
C#
Estrellas
512
Forks
99
Merge medio
5 d 6 h
PR fusionados (30 d)
5

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de Eventuous/eventuous

Todos los issues de Eventuous/eventuous

Issues similares

Más issues de C#

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.