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.
- CREATE NONCLUSTERED INDEX [nci_wi_DeploymentProcess_35A3485EF3D840CBCFC3E4B40089C38D] ON [dbo].[DeploymentProcess] ([IsFrozen], [OwnerId], [SpaceId]) WITH (ONLINE = ON)
- CREATE NONCLUSTERED INDEX [nci_wi_Release_E0A73111494D517A7404977D68536343] ON [dbo].[Release] ([ChannelId], [SpaceId]) WITH (ONLINE = ON)
- CREATE NONCLUSTERED INDEX [nci_wi_NuGetPackage_E9D9A90D7BC31DE0A7D25613C0F880B0] ON [dbo].[NuGetPackage] ([SpaceId], [PackageId]) INCLUDE ([JSON], [Version], [VersionBuild], [VersionMajor], [VersionMinor], [VersionRevision], [VersionSpecial]) WITH (ONLINE = ON)
- CREATE NONCLUSTERED INDEX [nci_wi_Interruption_A456BED17988F4C90409DF9BD4A5F290] ON [dbo].[Interruption] ([SpaceId], [DataVersion]) WITH (ONLINE = ON)
- 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)
- ALTER DATABASE [octopusdb] SET PARAMETERIZATION FORCED
Attached the screenshot reference!
Please let us know your thoughts on this!