ReadStreamForwards times out for long streams [MS SqlServer]

[yreynhout]

Today a former client brought to my attention that they were observing timeouts while reading a long stream using the ReadStreamForwards method. They were using a reasonable page size so the culprit was not trying to read with a page size of Int32.MaxValue. The query execution plan revealed the following:

A key lookup was performed which cost 99% of the time to perform the query - that didn't look right. It appears that all event rows in that particular stream were being read (1.8 million in this particular case - even if you frown upon that size, it may hit you at a lower number too) as part of the key lookup, explaining the time it took and thus the command timeout which kicked in as a consequence. The reason this happens is because it's missing an index on StreamIdInternal in the Messages table. Adding this

CREATE NONCLUSTERED INDEX [IX_Messages_StreamIdInternal] ON dbo.Messages ([StreamIdInternal])

will fix the slowness of this particular query:

     SELECT TOP(@count)
            dbo.Messages.StreamVersion,
            dbo.Messages.Position,
            dbo.Messages.Id,
            dbo.Messages.Created,
            dbo.Messages.[Type],
            dbo.Messages.JsonMetadata,
            dbo.Messages.JsonData
       FROM dbo.Messages
      WHERE dbo.Messages.StreamIdInternal = @streamIdInternal AND dbo.Messages.Position >= @position
   ORDER BY dbo.Messages.Position;

Courtesy of @ArneD and @koenmetsu