@tmasternak sorry to jump in and hijack this 8 months later…
We have a similar issue with some NSB sagas using SQL persistence (MSSQL). These sagas can be pretty short lived, and the number of them active at any one time may be small. That possibly highlights a design problem I know, however currently we are using a saga for this feature.
What we see is the optimizer ends up doing a scan on the PK, instead of using the nonclustered index on the correlation id, and if we have a burst of traffic we see deadlocks. As you’ve mentioned this doesn’t cause any consistency issues, however the deadlocks could be avoided.
We can resolve this issue by modifying the index that is generated on the correlation id to include the columns loaded for saga data which means the optimizer will now choose the non clustered index when the saga data is loaded i.e.
CREATE UNIQUE NONCLUSTERED INDEX [Index_Correlation_ContractId] ON [sales].[SomeSaaga]
(
[Correlation_ContractId] ASC
)
INCLUDE([Id],[Metadata],[Data],[SagaTypeVersion],[Concurrency])
WHERE ([Correlation_ContractId] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
Is there any reason why we shouldn’t modify the index in this way? And is this index definition something that has been considered by Particular for the index that is generated for sagas when using SQL Persistence MSSQL?
EDIT: obviously that’s going to double the storage for the table, but for our case that’s not a problem as the table is pretty small.