OutboxData index issue for MS Sql persistence

Architecture & Design
1

Hi,

We use MS Sql persistence and have index generated for Outbox_Create_sql that contains an index as Index_DispatchedAt. However, our RDS is still marking all OutboxData queries with

CREATE INDEX missing_index_6_5 
ON [OurService].[NServiceBus].[OutboxData ([Dispatched],[DispatchedAt])

We observe that the script is executed in each deployment and the index is there. However, we believe the current index in the generated sql is not fully correct:

create index Index_DispatchedAt 
on ' + @tableName + '(DispatchedAt) where Dispatched = 1;

Do we miss anything? Any insights?
Thanks in advance.

2

Hi!

The index created looks ok at first glance. What version of NServiceBus.Persistence.Sql are you on and what version of SqlServer are you using?

However, we believe the current index in the generated sql is not fully correct:
What part of the index looks off?

Cheers,

Andreas

3

We are on 6.6.0 version for NServiceBus.Persistence.Sql and on AWS RDS SqlServer Standar edition.

We do see the index on table in SqlServer too. But it’s reported as missing index as described in my message above.

4

What exact message is the RDS giving you? Can you perhaps post a screenshot?

Would you be able to test with the latest release to see if that fixes it?

5

Hi, we are using the query suggested in here to measure the perf:

My security limitations unfortunately not letting me to post an image to this site. But, as I mentioned, we are seeing for several services of ours the following suggestion:
CREATE INDEX missing_index_6_5 ON [OurService].[NServiceBus].[OutboxData ([Dispatched],[DispatchedAt])

As I said in my original message, we can observe that there is an index generated already. So, I am not sure what good it will do to try with a different version of the lib. From what I can tell, the script has not been changed in your end for a very long while. Also, we are not on NServiceBus 8 yet, so we cannot really test it with NServiceBus.SqlPersistence 7.x, right?

6

Thanks for the details. This looks like a bug on our end and I have raised Outbox DispatchedAt colum not indexed · Issue #1343 · Particular/NServiceBus.Persistence.Sql · GitHub to track a fix for it.

How does this impact your production environment other than being flagged by the RDS?

7

We have constant failures on outbox cleaning, popping up as errors in our logs. No customer impact that we know of, but that could potentially be the root cause.

Thanks for the update, I’ll be monitoring for a release / update.