Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

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

Open
  • Is something wrong?
#617 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 7 days

@nmummau is already working on this.

Since Oct 14, 2026.

  • #621 by @nmummau — open

Assessment

Difficulty
3/5
Estimated time
1-2 days
Newbie friendliness
25/100
Issue type
Refactor
Clarity
Clearly specified
Activity status
Stale
Tech stack
csharp, sql
Domain
backend, databases

Research direction

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.

Written by the indexing model from the issue text.

Description

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.

Dominant language
C#
Stars
512
Forks
99
Avg merge
5d 6h
Merged PRs (30d)
5

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from Eventuous/eventuous

All issues in Eventuous/eventuous

Similar issues

More C# issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.