Message receive query for SQL Server forces all data pages to be read, even if the table has no rows

[SzymonPobiega]

Describe the suggested improvement

Is your improvement related to a problem? Please describe.

SQL Server transport uses heap tables in order to optimize the inserts and deletes. The consequence of that design is the fact that the queue table contains much more pages then currently used -- the number reflects the maximum size of the queue table since it has been created (or shrank).

All these pages are part of the table but not actively used. The actively used pages are a subset that forms a linked list.

The assumption was that in this structure the receive query that deletes the record with lowest rowversion value first walks the index on rowversion up to a leaf that points to a page where the row exists and then that page is read. It appears that this is not the actual behavior of the SQL Server as documented below:

Given an empty table with lots of unused pages (Reserved space = 2416KB / 30000 pages) when selecting the oldest row

WITH message AS (
    SELECT TOP(1) *
    FROM {0} WITH (UPDLOCK, READPAST, ROWLOCK)
    ORDER BY RowVersion)
SELECT * FROM message

the entire table is logically read (logical reads = 3078)

Describe the suggested solution

Investigate why the Index_RowVersion is not used

Describe alternatives you've considered

Additional Context

No response