Reduce fragmentation on Event table

[flin-8]

Are you a customer of Octopus Deploy? Don't raise the issue here. Please contact our support team so we can triage your feature request, making sure it's handled appropriately.

Prerequisites

• [x] I have searched open and closed issues to make sure it isn't already requested
• [x] I have discussed this in #feature-discussion or #backlog and the consensus is that this is something we plan on adding in the near future
• [x] I have written a descriptive issue title
• [x] I have linked the original source of this feature request
• [x] I have tagged the issue appropriately (area/*, kind/enhancement)

The enhancement

What is the problem this solves or benefit it gives

Our Id columns (Events-123) are strings that not sortable due to the lack of zero padding. Having the clustered index based on this column means that the table fragments unnecessarily over time (for example when you jump from 99 -> 100).

This is problematic for the Event table because:

• We rebuild fragmented indexes after a database upgrade
• The Event table is large and the index takes a while to rebuild when fragmented
• We waste space in the non-clustered indexes because each record in those indexes has to store the string Id rather than AutoId which is much smaller.

Proposed solution

Change the clustered index to the existing identity column AutoId, so there should be no fragmentation.

[flin-8]

Release Note: Reduce fragmentation on Event table (This upgrade will take more time and disk space than normal because we are rebuilding Event table, which can be large. Please allow 1-2 hours and free space equal to the database size for the upgrade)

[flin-8]

Unfortunately we had to roll this back in https://github.com/OctopusDeploy/OctopusDeploy/pull/7659 due to the disk space usage