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

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

Abierto
  • ¿Algo no está bien?
#617 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
25/100
Tipo de issue
Refactorización
Claridad
Bien especificado
Estado de actividad
Estancado
Stack tecnológico
csharp, sql
Área
backend, databases

Línea de trabajo

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.

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

Descripción

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.

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.