Outbox DispatchedAt column not indexed

[andreasohlund]

Describe the bug

Description

Outbox records are deleted with where Dispatched = 'true' and DispatchedAt < @DispatchedBefore and since the outbox index only contains DispatchedAt (and Dispatched as the filter)
this leads to the database indicating (correctly) that the index isn't optimal.

This will also likely leads to poor performance deleting large number of outbox records.

Expected behavior

Index containing both the Dispached and the DispatchedAt column

Actual behavior

Index only contains the DispatchedAt column

Versions

All versions

Steps to reproduce

1. Enable tracing on the database, sqlserver in this case
2. Enable the outbox and process a few messages
3. Wait until cleanup happens
4. Notice the delete query not being able to use the index properly by using the instructions in https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions?view=sql-server-ver16

Relevant log output

No response

Additional Information

Workarounds

Possible solutions

Additional information

[sticl]

Not really sure if you're talking about the correct column?

https://github.com/Particular/NServiceBus.Persistence.Sql/blob/5d40a9014a11833f41548b23b1a45a72123dc1ed/src/ScriptBuilder/Outbox/Create_MsSqlServer.sql

DispatchedAt is indexed. You mean Dispatched?