Reg: Octopus DB Performance Recommendation from Azure

Octopus Server
, , ,
1

Hello Team,

We are using self hosted Octopus Deploy which is in 2019.9.10LTS version. Recently we have migrated the Octopus DB to Azure SQL (PaaS), previously it used to be in one of our SQL VMs. There are couple of performance recommendations came in Azure after we migrated, to create indexes and the last recommendation was referring to Forced Parameterization. I have pasted the queries which we got from the Azure Portal.

Are we good to implement this? Will there be any issues by creating these non-clustered indexes and Forced Parameterization.

  1. CREATE NONCLUSTERED INDEX [nci_wi_DeploymentProcess_35A3485EF3D840CBCFC3E4B40089C38D] ON [dbo].[DeploymentProcess] ([IsFrozen], [OwnerId], [SpaceId]) WITH (ONLINE = ON)
  2. CREATE NONCLUSTERED INDEX [nci_wi_Release_E0A73111494D517A7404977D68536343] ON [dbo].[Release] ([ChannelId], [SpaceId]) WITH (ONLINE = ON)
  3. CREATE NONCLUSTERED INDEX [nci_wi_NuGetPackage_E9D9A90D7BC31DE0A7D25613C0F880B0] ON [dbo].[NuGetPackage] ([SpaceId], [PackageId]) INCLUDE ([JSON], [Version], [VersionBuild], [VersionMajor], [VersionMinor], [VersionRevision], [VersionSpecial]) WITH (ONLINE = ON)
  4. CREATE NONCLUSTERED INDEX [nci_wi_Interruption_A456BED17988F4C90409DF9BD4A5F290] ON [dbo].[Interruption] ([SpaceId], [DataVersion]) WITH (ONLINE = ON)
  5. CREATE NONCLUSTERED INDEX [nci_wi_ServerTask_3FB386007814BCBEB1A9C2587E908590] ON [dbo].[ServerTask] ([ConcurrencyTag], [Name], [Id]) INCLUDE ([CompletedTime], [DataVersion], [Description], [DurationSeconds], [EnvironmentId], [ErrorMessage], [HasPendingInterruptions], [HasWarningsOrErrors], [JSON], [ProjectId], [QueueTime], [ServerNodeId], [SpaceId], [StartTime], [State], [TenantId]) WITH (ONLINE = ON)
  6. ALTER DATABASE [octopusdb] SET PARAMETERIZATION FORCED

Attached the screenshot reference!

OctopusDB_Azure
OctopusDB_Azure.png1679×513 47.7 KB

Please let us know your thoughts on this!

Cheers,
Karthikeyan

3

Hi Karthikeyan,

Thanks for getting in touch! Unfortunately I wouldn’t be able to provide any guarantees as to what the outcome would be, but you should be safe to implement these indexes and forced parameterization. However a couple of things to note: we have seen these recommendations do more harm than good in the past, and also note that these would be blown away during your next upgrade.

If you decide to take the leap, we’d be interested to hear if any of them provide noticeable improvement and we can possibly consider baking them into Octopus.

Don’t hesitate to reach out if you have any questions or concerns going forward. :slight_smile:

Best regards,

Kenny

1 Like
4

Sure! Thanks Kenny!

1 Like
5

Hi,

Did you go forward with these indexes?

Brgds

Jonas

6

Hi Jonas,

Thanks for getting in touch! I’m not aware of any of these mentioned indexes recommended by Azure being added into Octopus. I don’t think we’ve gotten any feedback on the impact of adding them in. As mentioned to Karthikeyan earlier, if you do decide to add them in yourself, we’d be interested to hear if it helps significantly. :slight_smile:

Best regards,

Kenny

7

Hello Jonas/Kenny,

We didn’t implement this as Octopus DB is completely managed by the Octopus itself. Also, we are running one single Octopus instance till Production, so we didn’t want to mess up with the DB :slight_smile:

Cheers,
Karthikeyan

2 Likes