Change outbox record indexes and colums

[ramonsmits]

This needs additional info WIP

We have the following indexes:

1. PK, clustered
2. MessageId, non clustered
3. Dispatched, non clustered
4. DispatchedAt, non clustered

We have the following queries:

SELECT *
FROM OutBoxRecord
WHERE MessageId IN (x,y)

Uses: (2)

UPDATE {0}
SET Dispatched = true, DispatchedAt = :date
WHERE MessageId IN ( :messageid, :qualifiedMessageId ) And Dispatched = false

Uses (2) and (3)

DELETE from {0}
WHERE Dispatched = true And DispatchedAt < :date"

Uses (3) and (4)

This is non optimal as multiple indexes need to be queried when updating and deleting which results in unneeded locking and can result in occasional deadlocks.

Proposal

• As Dispatched and DispatchedAt are either false and null or true and not null we can combine these in a single column resulting in one less index basically combining (3) and (4).
• The MessageId index maybe potentially improved if it included DispatchedAt value but as we are using NH this is not possible. The alternative is to add an additional index covering MessageId & DispatchedAt

References

https://github.com/Particular/NServiceBus.NHibernate/blob/master/src/NServiceBus.NHibernate/Outbox/EntityMappings.cs

Generated SQL:

CREATE TABLE [dbo].[OutboxRecord] (
    [Id]                  BIGINT         IDENTITY (1, 1) NOT NULL,
    [MessageId]           NVARCHAR (255) NOT NULL,
    [Dispatched]          BIT            DEFAULT ((0)) NOT NULL,
    [DispatchedAt]        DATETIME       NULL,
    [TransportOperations] NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    UNIQUE NONCLUSTERED ([MessageId] ASC)
);


GO
CREATE NONCLUSTERED INDEX [OutboxRecord_Dispatched_Idx]
    ON [dbo].[OutboxRecord]([Dispatched] ASC);


GO
CREATE NONCLUSTERED INDEX [OutboxRecord_DispatchedAt_Idx]
    ON [dbo].[OutboxRecord]([DispatchedAt] ASC);

[ramonsmits]

Moved this comment into new issue #319

[SzymonPobiega]

@ramonsmits is the partitioning something we can implement or should we create a guidance around it?

[ramonsmits]

I checked if this issue is still relevant to see if the indexes have changed but the indexes are still the same.

[SzymonPobiega]

@ramonsmits frankly I have no idea how to proceed with it according to our processes. I suggest you raise an issue in PlatDev. I doubt it could be treated as maintainer budget.