Missing indexes after upgrade

[dan.robertson]

After upgrading to 2232, our System Integrity checks are failing on a schema check. It appears to be missing indexes. Is there anything we can do to remedy this? Doing a reinstall from Octopus Manager hasn’t worked.

Thanks,
Dan

Missing item: IDX dbo.IX_DynamicInfrastructureLifecycle_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_DynamicInfrastructureLifecycle_BusinessProcess LastModified
Missing item: IDX dbo.IX_DynamicInfrastructureLifecycle_BusinessProcess NONCLUSTERED 0
Missing item: IDX dbo.IX_GitCredential_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_GitCredential_SpaceId SpaceId
Missing item: IDX dbo.IX_HalibutMessageQueueItem_SequenceNumber CLUSTERED 0
Missing item: IDX dbo.IX_HalibutMessageQueueItem_SequenceNumber Direction
Missing item: IDX dbo.IX_HalibutMessageQueueItem_SequenceNumber Endpoint
Missing item: IDX dbo.IX_HalibutMessageQueueItem_SequenceNumber SequenceNumber
Missing item: IDX dbo.IX_MachineHealthCheck_BusinessProcess BusinessProcessState
Missing item: IDX dbo.IX_MachineHealthCheck_BusinessProcess LastModified
Missing item: IDX dbo.IX_MachineHealthCheck_BusinessProcess NONCLUSTERED 0

[donny.bell]

Hi @dan.robertson,

Thank you for contacting Octopus Support. I’m sorry the Octopus Server upgrade resulted in missing indexes.

I can provide you with SQL queries to fix this. However, I’ll need to update my local test instance to create them. I’m working on this now and will reply once I have the queries built and tested later this afternoon.

I appreciate your patience while I work on this.

Best Regards,
Donny

[donny.bell]

Hi @dan.robertson,

Thank you for your patience.

Here is the SQL query you may run to create the missing indexes:

Please note, these index creation queries were generated for 2022.1.2232 only"

CREATE NONCLUSTERED INDEX [IX_DynamicInfrastructureLifecycle_BusinessProcess] ON [dbo].[DynamicInfrastructureLifecycle](LastModified DESC, BusinessProcessState ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_GitCredential_SpaceId] ON [dbo].[GitCredential](SpaceId ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE CLUSTERED INDEX [IX_HalibutMessageQueueItem_SequenceNumber] ON [dbo].[HalibutMessageQueueItem](SequenceNumber ASC, Endpoint ASC, Direction ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_MachineHealthCheck_BusinessProcess] ON [dbo].[MachineHealthCheck](LastModified DESC, BusinessProcessState ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF) ON [PRIMARY];

Let me know if that works as expected.

Best Regards,
Donny

[dan.robertson]

Brilliant. Thanks very much Donny. Will schedule it in.