Hacktoberfest 2026：维护者为十月标记出来的 issue，仍然开放、适合新手。 浏览 Hacktoberfest issue

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

未关闭
  • 哪里不对？
#618 1 条评论 0 个 reaction 已指派 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 小时
30 天内合并 PR
5

环境准备

  • 提供 Dockerfile 或 Docker Compose 文件
  • 没有 Pull Request 模板
  • 阅读贡献指南

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

Eventuous/eventuous 的其他 Issue

查看 Eventuous/eventuous 的全部 Issue

相似的 Issue

更多 C# Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。