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

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

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

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

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

2026年10月14日 から。

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

評価

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

調査の方向性

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.

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

説明

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.

主要言語
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 を短くまとめたダイジェスト。