Hacktoberfest 2026：メンテナが10月に向けて印を付けた、オープンで初心者向けの issue。 Hacktoberfest の issue を見る

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

オープン
  • 何か問題がありますか？
#617 コメント 1 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 7 日以内に返信

@nmummau がすでに取り組んでいます。

2026年10月14日 から。

  • #621 @nmummau による — オープン

評価

難易度
3/5
見積もり時間
1〜2日
初心者へのやさしさ
25/100
issue の種類
リファクタリング
明瞭さ
明確に書かれている
活発さ
停滞
技術スタック
csharp, sql
領域
backend, databases

調査の方向性

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.

索引モデルが issue の本文から書いたものです。

説明

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.

主要言語
C#
スター
512
フォーク
99
平均マージ
5日 6時間
マージ済み PR（30日）
5

環境構築

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

Eventuous/eventuous のほかの issue

Eventuous/eventuous の issue をすべて見る

似ている issue

C# の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。