This is part of the NServiceBus Upgrade Guide from Version 5 to 6, which also includes the following individual upgrade guides for specific components:
- Assembly Scanning Changes in NServiceBus Version 6
- No Async Suffix
- Dependency Injection Changes in NServiceBus Version 6
- Deprecated TransportMessage in NServiceBus Version 6
- Endpoint API changes in NServiceBus Version 6
- Extension Seam Changes in NServiceBus Version 6
- Migrate handlers and sagas to Version 6
- Header API changes in NServiceBus Version 6
- Messaging Changes in NServiceBus Version 6
- Moving away from IBus in Version 6
- Recoverability Changes in Version 6
- Serialization Changes in NServiceBus Version 6
- Subscription Changes in NServiceBus Version 6
- Transaction Configuration Changes in NServiceBus Version 6
- Azure Service Bus Transport (Legacy) Upgrade Version 6 to 7
- RabbitMQ Transport Upgrade Version 3 to 4
- SQL Server Transport Upgrade Version 2 to 3
- SQL Server Transport Upgrade - Supporting Unicode in Headers
- Upgrade from NServiceBus Azure Version 6
- NHibernate Persistence Upgrade Version 6 to 7
- NHibernate Persistence - Resolving incorrect timeout table indexes
- RavenDB Persistence Upgrade from 3 to 4
- Moving to the DataBus AzureBlobStorage Package
- Azure Cloud Services Host Upgrade Version 6 to 7
- NServiceBus.Azure package deprecated
- Gateway Upgrade Version 1 to 2
- NServiceBus Testing Upgrade Version 5 to 6
- Callback Changes in NServiceBus Version 6
- Migrating the distributor to use sender-side distribution
- Tool and Helper Changes in NServiceBus Version 6
This guidance explains how to resolve an incorrectly created index when using a custom NHibernate configuration. This is described in the issue Incorrect schema creation for timeout entity causes performance degradation.
This issue causes performance degradation if the table contains a large number of rows. Inserts and queries are inefficient due to the incorrect order of columns in the index. This results in unnecessary locking which limits the processing throughput of timeouts.
If any of the supported affected minor versions (7.1.x, 7.0.x, or 6.2.8) are used, these should be updated to the latest patch release. If an older non-supported affected version is used, this should be updated to a newer minor (in case of 6.1.x or 6.0.x) or major version (any version prior to 6.x).
- Update to latest patch release.
- Deploy the new version.
- Check if a warning related to this schema issue is visible.
- Or manually inspect the schema in the database.
- Follow the procedure on how to resolve schema issues for the database engine used (Microsoft SQL Server or Oracle).
- If any other database engine is used, then these changes must be applied manually.
If there are endpoints that created an incorrect index definition, then this is detected in all fixed supported versions for 6.2.x, 7.0.x and 7.1.x. The detection routine is run when the endpoint instance is created and started. If that instance is affected, the following warning is logged:
Could not find TimeoutEntity_EndpointIdx index. This may cause significant performance degradation of message deferral. Consult NServiceBus NHibernate persistence documentation for details on how to create this index.
If this log event is written to the log file, then read the following guidance on how to apply corrections.
Any of the following issues can be present:
TimeoutEntityhas a clustered primary key (on Microsoft SQL Server).
TimeoutEntity_EndpointIdxis non-clustered (on Microsoft SQL Server).
TimeoutEntity_EndpointIdxhas an incorrect column order (should be Endpoint, Time).
The approach to applying corrections depends on the database engine that is used.
This assumes that both the index column order and clustered index are incorrect. To resolve this, all existing indexes need to be dropped and recreated.
dboschema with the custom schema identifier.
declare @schema nvarchar(max) = 'dbo' -- Update 'dbo' with custom schema if needed declare @sql nvarchar(max) declare @pkindex nvarchar(max) select @pkindex = si.name from sys.tables st join sys.indexes si on st.object_id = si.object_id join sys.schemas ss on st.schema_id = ss.schema_id where st.name = 'TimeoutEntity' and si.is_primary_key = 1 and ss.name = @schema begin tran select @sql = 'drop index[TimeoutEntity_SagaIdIdx] on [' + @schema + '].[TimeoutEntity]' exec sp_executeSQL @sql select @sql = 'drop index [TimeoutEntity_EndpointIdx] on [' + @schema + '].[TimeoutEntity]' exec sp_executeSQL @sql select @sql = 'alter table [' + @schema + '].[TimeoutEntity] drop constraint ' + @pkindex exec sp_executeSQL @sql select @sql = 'alter table [' + @schema + '].[TimeoutEntity] add constraint ' + @pkindex + ' primary key nonclustered (Id)' exec sp_executeSQL @sql select @sql = 'create nonclustered index [TimeoutEntity_SagaIdIdx] on [' + @schema + '].[TimeoutEntity]([SagaId]);' exec sp_executeSQL @sql select @sql = 'create clustered index [TimeoutEntity_EndpointIdx] on [' + @schema + '].[TimeoutEntity]([Endpoint], [Time]);' exec sp_executeSQL @sql commit tran
The incorrect index definition on Oracle only applies to the column order. An existing
TIMEOUTENTITY_ENDPOINTIDX index has to be dropped, and a new index with correct column order needs to be created.
drop index TIMEOUTENTITY_ENDPOINTIDX; create index TIMEOUTENTITY_ENDPOINTIDX on TIMEOUTENTITY (ENDPOINT asc, TIME asc);