search

Particular / NServiceBus.Persistence.Sql

Native SQL Persistence for NServiceBus
https://docs.particular.net/persistence/sql/
Other
36 stars 27 forks source link

Outbox DispatchedAt column not indexed #1343

Open andreasohlund opened 9 months ago

andreasohlund commented 9 months ago

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 commented 1 month ago

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?