Octopus Server upgrade assess

dalmiro.granas · 9 December 2021 16:41

Hi team

We are having some performance issues in our Octopus instance (pages taking a full minut to load) and I’m working through the basics to get the server in a better place.

With the above in mind, we just upgraded from 2021.1 to 2021.3 and below you can find the Database schema upgrade portion of the upgrade logs.

I was wondering if one of you can take look at them and let us know if the timings look correct, or if there’s anything that you consider took way longer than expected that might point us into the right direction to fix the performance issues.

====================================================================================
Starting database schema upgrade...
====================================================================================

Executing pre-upgrade scripts...
Executing TSQL Database Server script 'Script0000 - Database options.sql'
Enabling read committed snapshot isolation on OctopusDB
Read committed snapshot isolation is already on
Executing schema upgrade scripts...
Beginning transaction
Checking whether journal table exists..
Executing .NET Database Server script 'Script0276CreateRelatedDocumentIdPrefixColumn'
Checking whether journal table exists..
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
Still working after 1 minute...
Executing .NET Database Server script 'Script0278SetAzureScriptSyntax - ActionTemplate'
Executing .NET Database Server script 'Script0278SetAzureScriptSyntax - ActionTemplateVersion'
Executing .NET Database Server script 'Script0278SetAzureScriptSyntax - DeploymentProcess'
Executing .NET Database Server script 'Script0278SetAzureScriptSyntax - RunbookProcess'
Executing .NET Database Server script 'Script0279AddLifecycleToReleases'
Executing .NET Database Server script 'Script0280AddGitEntityStash'
Executing .NET Database Server script 'Script0282DropTenantProjectView'
Executing .NET Database Server script 'Script0283AddEventRelatedDocumentIndexes'
Executing .NET Database Server script 'Script0283RemoveDollarTypeFromJson'
Executing .NET Database Server script 'Script0284AddRunbookRunRelatedMachine'
Executing .NET Database Server script 'Script0285FixInvalidReleasedByCertificates'
Executing .NET Database Server script 'Script0286CreateMessageBusEventTable'
Executing .NET Database Server script 'Script0286SetAWSOctopusUseBundledTooling - ActionTemplate'
Executing .NET Database Server script 'Script0286SetAWSOctopusUseBundledTooling - ActionTemplateVersion'
Executing .NET Database Server script 'Script0286SetAWSOctopusUseBundledTooling - DeploymentProcess'
Executing .NET Database Server script 'Script0286SetAWSOctopusUseBundledTooling - RunbookProcess'
Executing .NET Database Server script 'Script0287CreateMessageBusCursorTable'
Executing .NET Database Server script 'Script0287SetAzureAppServiceOctopusUseBundledTooling - ActionTemplate'
Executing .NET Database Server script 'Script0287SetAzureAppServiceOctopusUseBundledTooling - ActionTemplateVersion'
Executing .NET Database Server script 'Script0287SetAzureAppServiceOctopusUseBundledTooling - DeploymentProcess'
Executing .NET Database Server script 'Script0287SetAzureAppServiceOctopusUseBundledTooling - RunbookProcess'
Executing .NET Database Server script 'Script0288SetAzureCloudServiceOctopusUseBundledTooling - ActionTemplate'
Executing .NET Database Server script 'Script0288SetAzureCloudServiceOctopusUseBundledTooling - ActionTemplateVersion'
Executing .NET Database Server script 'Script0288SetAzureCloudServiceOctopusUseBundledTooling - DeploymentProcess'
Executing .NET Database Server script 'Script0288SetAzureCloudServiceOctopusUseBundledTooling - RunbookProcess'
Executing .NET Database Server script 'Script0289SetAzurePowerShellOctopusUseBundledTooling - ActionTemplate'
Executing .NET Database Server script 'Script0289SetAzurePowerShellOctopusUseBundledTooling - ActionTemplateVersion'
Executing .NET Database Server script 'Script0289SetAzurePowerShellOctopusUseBundledTooling - DeploymentProcess'
Executing .NET Database Server script 'Script0289SetAzurePowerShellOctopusUseBundledTooling - RunbookProcess'
Executing .NET Database Server script 'Script0290SetAzureResourceGroupOctopusUseBundledTooling - ActionTemplate'
Executing .NET Database Server script 'Script0290SetAzureResourceGroupOctopusUseBundledTooling - ActionTemplateVersion'
Executing .NET Database Server script 'Script0290SetAzureResourceGroupOctopusUseBundledTooling - DeploymentProcess'
Executing .NET Database Server script 'Script0290SetAzureResourceGroupOctopusUseBundledTooling - RunbookProcess'
Executing .NET Database Server script 'Script0291SetAzureServiceFabricAppOctopusUseBundledTooling - ActionTemplate'
Executing .NET Database Server script 'Script0291SetAzureServiceFabricAppOctopusUseBundledTooling - ActionTemplateVersion'
Executing .NET Database Server script 'Script0291SetAzureServiceFabricAppOctopusUseBundledTooling - DeploymentProcess'
Executing .NET Database Server script 'Script0291SetAzureServiceFabricAppOctopusUseBundledTooling - RunbookProcess'
Executing .NET Database Server script 'Script0292SetAzureWebAppOctopusUseBundledTooling - ActionTemplate'
Executing .NET Database Server script 'Script0292SetAzureWebAppOctopusUseBundledTooling - ActionTemplateVersion'
Executing .NET Database Server script 'Script0292SetAzureWebAppOctopusUseBundledTooling - DeploymentProcess'
Executing .NET Database Server script 'Script0292SetAzureWebAppOctopusUseBundledTooling - RunbookProcess'
Executing .NET Database Server script 'Script0293SetAzureServiceFabricPowerShellOctopusUseBundledTooling - ActionTemplate'
Executing .NET Database Server script 'Script0293SetAzureServiceFabricPowerShellOctopusUseBundledTooling - ActionTemplateVersion'
Executing .NET Database Server script 'Script0293SetAzureServiceFabricPowerShellOctopusUseBundledTooling - DeploymentProcess'
Executing .NET Database Server script 'Script0293SetAzureServiceFabricPowerShellOctopusUseBundledTooling - RunbookProcess'
Executing .NET Database Server script 'Script0294RemoveOrphanedSpaceData'
Executing .NET Database Server script 'Script0294UpdateAwsEcsStepPackageVersions - ActionTemplate'
Executing .NET Database Server script 'Script0294UpdateAwsEcsStepPackageVersions - ActionTemplateVersion'
Executing .NET Database Server script 'Script0294UpdateAwsEcsStepPackageVersions - DeploymentProcess'
Executing .NET Database Server script 'Script0294UpdateAwsEcsStepPackageVersions - RunbookProcess'
Executing .NET Database Server script 'Script0295ReAddEventRelatedDocumentIndexes'
Executing .NET Database Server script 'Script0295UpdateAzureBlobStorageUploadStepPackageVersions - ActionTemplate'
Executing .NET Database Server script 'Script0295UpdateAzureBlobStorageUploadStepPackageVersions - ActionTemplateVersion'
Executing .NET Database Server script 'Script0295UpdateAzureBlobStorageUploadStepPackageVersions - DeploymentProcess'
Executing .NET Database Server script 'Script0295UpdateAzureBlobStorageUploadStepPackageVersions - RunbookProcess'
Executing .NET Database Server script 'Script0296UpdateStepPackageDeploymentTargetVersions'
Executing .NET Database Server script 'Script0297AddEntityPropertiesToProject'
Executing .NET Database Server script 'Script0297CreateBlobTable'
Warning! The maximum key length for a clustered index is 900 bytes. The index 'IX_Blob_BlobId' has maximum length of 1040 bytes. For some combination of large values, the insert/update operation will fail.
Executing .NET Database Server script 'Script0297RemoveLifecycleFromReleases'
Executing .NET Database Server script 'Script0297UpdateAwsEcsStepPackageVersions - ActionTemplate'
Executing .NET Database Server script 'Script0297UpdateAwsEcsStepPackageVersions - ActionTemplateVersion'
Executing .NET Database Server script 'Script0297UpdateAwsEcsStepPackageVersions - DeploymentProcess'
Executing .NET Database Server script 'Script0297UpdateAwsEcsStepPackageVersions - RunbookProcess'
Executing .NET Database Server script 'Script0298AddEntityPropertiesToServerTask'
Executing .NET Database Server script 'Script0298RevertVCSChannels'
Executing .NET Database Server script 'Script0298UpdateAzureBlobStorageUploadStepPackageVersions - ActionTemplate'
Executing .NET Database Server script 'Script0298UpdateAzureBlobStorageUploadStepPackageVersions - ActionTemplateVersion'
Executing .NET Database Server script 'Script0298UpdateAzureBlobStorageUploadStepPackageVersions - DeploymentProcess'
Executing .NET Database Server script 'Script0298UpdateAzureBlobStorageUploadStepPackageVersions - RunbookProcess'
Executing .NET Database Server script 'Script0299MakeServerTaskABusinessProcess'
Executing .NET Database Server script 'Script0299UpdateStepPackageDeploymentTargetVersions'
Executing .NET Database Server script 'Script0300AlterBlobIdLength'
Executing .NET Database Server script 'Script0300FixUnknownVariableTypes'
Executing .NET Database Server script 'Script0300RemovePrometheusConfiguration'
Executing .NET Database Server script 'Script0301AddBehaviouralTelemetryDefaultConfiguration'
Executing .NET Database Server script 'Script0301FixPackageAcquisitionForSteps - DeploymentProcess'
Executing .NET Database Server script 'Script0301FixPackageAcquisitionForSteps - RunbookProcess'
Executing .NET Database Server script 'Script0302SetAzureAppServiceToPackageAcquisitionLocationNotAcquiredIfUsingContainerReference - ActionTemplate'
Executing .NET Database Server script 'Script0302SetAzureAppServiceToPackageAcquisitionLocationNotAcquiredIfUsingContainerReference - ActionTemplateVersion'
Executing .NET Database Server script 'Script0302SetAzureAppServiceToPackageAcquisitionLocationNotAcquiredIfUsingContainerReference - DeploymentProcess'
Executing .NET Database Server script 'Script0302SetAzureAppServiceToPackageAcquisitionLocationNotAcquiredIfUsingContainerReference - RunbookProcess'
Executing .NET Database Server script 'Script0303IncreaseBuildInfoPackageColumn'
Executing .NET Database Server script 'Script0303RemoveAzureBlobStorageUploadActions - DeploymentProcess'
Executing .NET Database Server script 'Script0303RemoveAzureBlobStorageUploadActions - RunbookProcess'
Executing .NET Database Server script 'Script0304RemoveAzureBlobStorageUploadActionTemplates'
Executing .NET Database Server script 'Script0305RemoveAzureBlobContainerDeploymentTargets'
Executing .NET Database Server script 'Script0306UpdateECSStepPackageVersion - ActionTemplate'
Executing .NET Database Server script 'Script0306UpdateECSStepPackageVersion - ActionTemplateVersion'
Executing .NET Database Server script 'Script0306UpdateECSStepPackageVersion - DeploymentProcess'
Executing .NET Database Server script 'Script0306UpdateECSStepPackageVersion - RunbookProcess'
Executing .NET Database Server script 'Script0307UpdateECSTargetStepPackageVersion'
Executing .NET Database Server script 'Script0308EnableStepUIFramework'
Executing .NET Database Server script 'Script0309MarkPreExistingTasksAsCompleted'
Executing .NET Database Server script 'Script0310IncludeSpaceIdInServerTaskIndex'
Executing .NET Database Server script 'Script0311CreateMachineScriptTaskTable'
Executing .NET Database Server script 'Script0311EnsureServerTaskQueueTimeIsNotMinValue'
Executing post-upgrade scripts...
Executing TSQL Database Server script 'Script0001 - Rebuild indexes.sql'
Still working after 1 minute...
Still working after 2 minutes...
Still working after 3 minutes...
Performing maintenance on SQL indexes
Querying index fragmentation
Reorganizing 25 fragmented indexes
Reorganizing index PK_Account_Id (30%) on table dbo.Account (1/25)
Reorganizing index PK_Account_Id took 0 seconds
Reorganizing index IX_Deployment_UpdateDeploymentHistory (30%) on table dbo.Deployment (2/25)
Reorganizing index IX_Deployment_UpdateDeploymentHistory took 2 seconds
Reorganizing index IX_Release_ProjectId_Version_Assembled (29%) on table dbo.Release (3/25)
Reorganizing index IX_Release_ProjectId_Version_Assembled took 1 seconds
Reorganizing index IX_Release_ProjectId_ChannelId_Assembled (28%) on table dbo.Release (4/25)
Reorganizing index IX_Release_ProjectId_ChannelId_Assembled took 2 seconds
Reorganizing index PK_Event_Id (27%) on table dbo.Event (5/25)
Reorganizing index PK_Event_Id took 185 seconds
Reorganizing index IX_Deployment_Project (25%) on table dbo.Deployment (6/25)
Reorganizing index IX_Deployment_Project took 1 seconds
Reorganizing index IX_DeploymentProcess_SpaceId (25%) on table dbo.DeploymentProcess (7/25)
Reorganizing index IX_DeploymentProcess_SpaceId took 0 seconds
Reorganizing index IX_Interruption_DataVersion (25%) on table dbo.Interruption (8/25)
Reorganizing index IX_Interruption_DataVersion took 0 seconds
Reorganizing index IX_RunbookRun_UpdateRunbookRunHistory (25%) on table dbo.RunbookRun (9/25)
Reorganizing index IX_RunbookRun_UpdateRunbookRunHistory took 0 seconds
Reorganizing index PK_Deployment_Id (0%) on table dbo.Deployment (10/25)
Reorganizing index PK_Deployment_Id took 33 seconds
Reorganizing index PK_Interruption_Id (23%) on table dbo.Interruption (11/25)
Reorganizing index PK_Interruption_Id took 0 seconds
Reorganizing index IX_Event_UserAgent (19%) on table dbo.Event (12/25)
Reorganizing index IX_Event_UserAgent took 7 seconds
Reorganizing index PK_Tenant_Id (18%) on table dbo.Tenant (13/25)
Reorganizing index PK_Tenant_Id took 0 seconds
Reorganizing index IX_DeploymentHistory_Created (17%) on table dbo.DeploymentHistory (14/25)
Reorganizing index IX_DeploymentHistory_Created took 2 seconds
Reorganizing index IX_Release_DataVersion (16%) on table dbo.Release (15/25)
Still working after 4 minutes...
Reorganizing index IX_Release_DataVersion took 0 seconds
Reorganizing index IX_DeploymentHistory_SpaceId (15%) on table dbo.DeploymentHistory (16/25)
Reorganizing index IX_DeploymentHistory_SpaceId took 1 seconds
Reorganizing index PK_DeploymentRelatedMachine (14%) on table dbo.DeploymentRelatedMachine (17/25)
Reorganizing index PK_DeploymentRelatedMachine took 3 seconds
Reorganizing index IX_DeploymentSettings_SpaceId_ProjectId (14%) on table dbo.DeploymentSettings (18/25)
Reorganizing index IX_DeploymentSettings_SpaceId_ProjectId took 0 seconds
Reorganizing index IX_Event_SpaceId (14%) on table dbo.Event (19/25)
Reorganizing index IX_Event_SpaceId took 9 seconds
Reorganizing index PK_Release_Id (13%) on table dbo.Release (20/25)
Reorganizing index PK_Release_Id took 2 seconds
Reorganizing index PK_DeploymentHistory_DeploymentId (13%) on table dbo.DeploymentHistory (21/25)
Reorganizing index PK_DeploymentHistory_DeploymentId took 6 seconds
Reorganizing index PK_CommunityActionTemplate_Id (12%) on table dbo.CommunityActionTemplate (22/25)
Reorganizing index PK_CommunityActionTemplate_Id took 0 seconds
Reorganizing index IX_DeploymentRelatedMachine_Machine (12%) on table dbo.DeploymentRelatedMachine (23/25)
Reorganizing index IX_DeploymentRelatedMachine_Machine took 1 seconds
Reorganizing index PK_DeploymentProcess_Id (12%) on table dbo.DeploymentProcess (24/25)
Reorganizing index PK_DeploymentProcess_Id took 1 seconds
Reorganizing index IX_ServerTask_ProjectDataVersion (12%) on table dbo.ServerTask (25/25)
Reorganizing index IX_ServerTask_ProjectDataVersion took 6 seconds
Rebuilding 60 heavily fragmented indexes
Rebuilding index IX_RunbookRun_Index (91%) on table dbo.RunbookRun (1/60)
Rebuilding index IX_RunbookRun_Index took 0 seconds
Rebuilding index IX_Machine_DataVersion (90%) on table dbo.Machine (2/60)
Rebuilding index IX_Machine_DataVersion took 0 seconds
Rebuilding index IX_RunbookRun_RunbookId (84%) on table dbo.RunbookRun (3/60)
Rebuilding index IX_RunbookRun_RunbookId took 0 seconds
Rebuilding index IX_RunbookRun_TenantId (83%) on table dbo.RunbookRun (4/60)
Rebuilding index IX_RunbookRun_TenantId took 0 seconds
Rebuilding index PK_Machine_Id (81%) on table dbo.Machine (5/60)
Rebuilding index PK_Machine_Id took 0 seconds
Rebuilding index PK_ProjectTrigger_Id (78%) on table dbo.ProjectTrigger (6/60)
Rebuilding index PK_ProjectTrigger_Id took 0 seconds
Rebuilding index IX_RunbookRun_ProjectId (71%) on table dbo.RunbookRun (7/60)
Rebuilding index IX_RunbookRun_ProjectId took 0 seconds
Rebuilding index PK_Project_Id (69%) on table dbo.Project (8/60)
Rebuilding index PK_Project_Id took 0 seconds
Rebuilding index IX_RunbookRun_SpaceId (68%) on table dbo.RunbookRun (9/60)
Rebuilding index IX_RunbookRun_SpaceId took 0 seconds
Rebuilding index IX_RunbookSnapshot_FrozenRunbookProcessId (67%) on table dbo.RunbookSnapshot (10/60)
Rebuilding index IX_RunbookSnapshot_FrozenRunbookProcessId took 0 seconds
Rebuilding index PK_Runbook_Id (67%) on table dbo.Runbook (11/60)
Rebuilding index PK_Runbook_Id took 0 seconds
Rebuilding index IX_Release_ProjectDeploymentProcessSnapshotId (64%) on table dbo.Release (12/60)
Rebuilding index IX_Release_ProjectDeploymentProcessSnapshotId took 1 seconds
Rebuilding index IX_Release_Assembled (64%) on table dbo.Release (13/60)
Rebuilding index IX_Release_Assembled took 0 seconds
Rebuilding index IX_Release_SpaceId (63%) on table dbo.Release (14/60)
Rebuilding index IX_Release_SpaceId took 0 seconds
Rebuilding index IX_VariableSet_SpaceId (56%) on table dbo.VariableSet (15/60)
Rebuilding index IX_VariableSet_SpaceId took 0 seconds
Rebuilding index UQ_ReleaseVersionUnique (55%) on table dbo.Release (16/60)
Rebuilding index UQ_ReleaseVersionUnique took 0 seconds
Rebuilding index PK_RunbookSnapshot_Id (53%) on table dbo.RunbookSnapshot (17/60)
Rebuilding index PK_RunbookSnapshot_Id took 0 seconds
Rebuilding index IX_RelatedDocument_RelatedDocumentId (53%) on table dbo.RelatedDocument (18/60)
Rebuilding index IX_RelatedDocument_RelatedDocumentId took 0 seconds
Rebuilding index IX_Release_SpaceId_ProjectId_ChannelId_Assembled (52%) on table dbo.Release (19/60)
Rebuilding index IX_Release_SpaceId_ProjectId_ChannelId_Assembled took 0 seconds
Rebuilding index IX_ServerTask_TaskQueue_QueueTimeState (51%) on table dbo.ServerTask (20/60)
Rebuilding index IX_ServerTask_TaskQueue_QueueTimeState took 1 seconds
Rebuilding index IX_ServerTask_TaskQueue_GetActiveConcurrencyTags (51%) on table dbo.ServerTask (21/60)
Rebuilding index IX_ServerTask_TaskQueue_GetActiveConcurrencyTags took 1 seconds
Rebuilding index IX_DeploymentSettings_DataVersion (50%) on table dbo.DeploymentSettings (22/60)
Rebuilding index IX_DeploymentSettings_DataVersion took 0 seconds
Rebuilding index UQ_SnapshotNameUnique (50%) on table dbo.RunbookSnapshot (23/60)
Rebuilding index UQ_SnapshotNameUnique took 0 seconds
Rebuilding index IX_RunbookSnapshot_Assembled (50%) on table dbo.RunbookSnapshot (24/60)
Rebuilding index IX_RunbookSnapshot_Assembled took 0 seconds
Rebuilding index IX_TenantVariable_TenantId (50%) on table dbo.TenantVariable (25/60)
Rebuilding index IX_TenantVariable_TenantId took 0 seconds
Rebuilding index IX_TenantVariable_SpaceId (50%) on table dbo.TenantVariable (26/60)
Rebuilding index IX_TenantVariable_SpaceId took 0 seconds
Rebuilding index PK_Artifact_Id (50%) on table dbo.Artifact (27/60)
Rebuilding index PK_Artifact_Id took 0 seconds
Rebuilding index PK_Certificate_Id (50%) on table dbo.Certificate (28/60)
Rebuilding index PK_Certificate_Id took 0 seconds
Rebuilding index PK_Configuration_Id (50%) on table dbo.Configuration (29/60)
Rebuilding index PK_Configuration_Id took 0 seconds
Rebuilding index IX_ActionTemplateVersion_LatestActionTemplateId (50%) on table dbo.ActionTemplateVersion (30/60)
Rebuilding index IX_ActionTemplateVersion_LatestActionTemplateId took 0 seconds
Rebuilding index IX_ActionTemplateVersion_SpaceId (50%) on table dbo.ActionTemplateVersion (31/60)
Rebuilding index IX_ActionTemplateVersion_SpaceId took 0 seconds
Rebuilding index IX_User_EmailAddress (50%) on table dbo.User (32/60)
Rebuilding index IX_User_EmailAddress took 0 seconds
Rebuilding index IX_User_DisplayName (50%) on table dbo.User (33/60)
Rebuilding index IX_User_DisplayName took 0 seconds
Rebuilding index IX_User_IdentificationToken (50%) on table dbo.User (34/60)
Rebuilding index IX_User_IdentificationToken took 0 seconds
Rebuilding index IX_Project_DataVersion (50%) on table dbo.Project (35/60)
Rebuilding index IX_Project_DataVersion took 0 seconds
Rebuilding index PK_Team_Id (50%) on table dbo.Team (36/60)
Rebuilding index PK_Team_Id took 0 seconds
Rebuilding index PK_VariableSet_Id (0%) on table dbo.VariableSet (37/60)
Rebuilding index PK_VariableSet_Id took 0 seconds
Rebuilding index PK_RunbookRun_Id (48%) on table dbo.RunbookRun (38/60)
Rebuilding index PK_RunbookRun_Id took 0 seconds
Rebuilding index PK_RunbookRunHistory_RunbookRunId (47%) on table dbo.RunbookRunHistory (39/60)
Rebuilding index PK_RunbookRunHistory_RunbookRunId took 1 seconds
Rebuilding index IX_ServerTask_SpaceId (44%) on table dbo.ServerTask (40/60)
Rebuilding index IX_ServerTask_SpaceId took 1 seconds
Rebuilding index IX_ServerTask_TaskQueue_PopTask (44%) on table dbo.ServerTask (41/60)
Rebuilding index IX_ServerTask_TaskQueue_PopTask took 2 seconds
Rebuilding index PK_RunbookProcess_Id (44%) on table dbo.RunbookProcess (42/60)
Rebuilding index PK_RunbookProcess_Id took 0 seconds
Rebuilding index IX_DeploymentRelatedMachine_Deployment (44%) on table dbo.DeploymentRelatedMachine (43/60)
Rebuilding index IX_DeploymentRelatedMachine_Deployment took 2 seconds
Rebuilding index IX_Deployment_TenantId (43%) on table dbo.Deployment (44/60)
Rebuilding index IX_Deployment_TenantId took 0 seconds
Rebuilding index PK_User_Id (42%) on table dbo.User (45/60)
Rebuilding index PK_User_Id took 0 seconds
Rebuilding index IX_ServerTask_Common (41%) on table dbo.ServerTask (46/60)
Rebuilding index IX_ServerTask_Common took 3 seconds
Rebuilding index IX_Deployment_Index (40%) on table dbo.Deployment (47/60)
Rebuilding index IX_Deployment_Index took 1 seconds
Rebuilding index PK_ServerTask_Id (0%) on table dbo.ServerTask (48/60)
Rebuilding index PK_ServerTask_Id took 3 seconds
Rebuilding index PK_SchemaVersions_Id (40%) on table dbo.SchemaVersions (49/60)
Rebuilding index PK_SchemaVersions_Id took 0 seconds
Rebuilding index IX_Event_CommonSearch (0%) on table dbo.Event (50/60)
Rebuilding index IX_Event_CommonSearch took 11 seconds
Rebuilding index IX_Deployment_SpaceId (35%) on table dbo.Deployment (51/60)
Rebuilding index IX_Deployment_SpaceId took 1 seconds
Rebuilding index IX_Event_Category_AutoId (35%) on table dbo.Event (52/60)
Rebuilding index IX_Event_Category_AutoId took 6 seconds
Rebuilding index IX_Deployment_ChannelId (34%) on table dbo.Deployment (53/60)
Rebuilding index IX_Deployment_ChannelId took 1 seconds
Rebuilding index PK_Feed_Id (33%) on table dbo.Feed (54/60)
Rebuilding index PK_Feed_Id took 0 seconds
Rebuilding index PK_TagSet_Id (33%) on table dbo.TagSet (55/60)
Rebuilding index PK_TagSet_Id took 0 seconds
Rebuilding index PK_DeploymentSettings_Id (33%) on table dbo.DeploymentSettings (56/60)
Rebuilding index PK_DeploymentSettings_Id took 0 seconds
Rebuilding index UQ_CommunityActionTemplateExternalId (33%) on table dbo.CommunityActionTemplate (57/60)
Rebuilding index UQ_CommunityActionTemplateExternalId took 0 seconds
Rebuilding index UQ_MachineNameUniquePerSpace (33%) on table dbo.Machine (58/60)
Rebuilding index UQ_MachineNameUniquePerSpace took 0 seconds
Rebuilding index UQ_UserUsernameUnique (33%) on table dbo.User (59/60)
Rebuilding index UQ_UserUsernameUnique took 0 seconds
Rebuilding index IX_RunbookRunHistory_IsPublished (31%) on table dbo.RunbookRunHistory (60/60)
Rebuilding index IX_RunbookRunHistory_IsPublished took 1 seconds
Finished performing maintenance on SQL indexes. The whole process took 300 seconds
RecordsAffected: 238
====================================================================================
Database schema upgrade completed taking 9 minutes
====================================================================================

I know you won’t be able to make much from this log, but just an opinion would do in this case

Cheers!

Dalmiro

garrett.dass · 9 December 2021 16:53

Hi @dalmiro.granas,

Thanks for your question and good to hear from you.

I am going to run this by the team, but in the meantime could you run a System Integrity check for us under Configuration > Diagnostics > System Integrity check. We’ve seen a few indexes go missing in the schema during upgrades that cause performance issues, could you send us the output list if this is the case?

Looking forward to hearing back.

Regards,
Garrett

dalmiro.granas · 9 December 2021 18:03

This is the output of the System Integrity check . Is this what you were looking for?

Task ID:        ServerTasks-1062898
Related IDs:    
Task status:    Success
Task queued:    Thursday, 09 December 2021 6:02:05 PM +00:00
Task started:   Thursday, 09 December 2021 6:02:05 PM +00:00
Task completed: Thursday, 09 December 2021 6:02:28 PM +00:00
Task duration:  23 seconds
Server version: 2021.3.8275
Server node:    my-server-node

                    | Success: Check System Integrity
18:02:28   Info     |   Unexpected related document id format....................... [ Passed ]
18:02:28   Info     |   Schema...................................................... [ Passed ]
18:02:28   Info     |   Release channel belongs to the appropriate project.......... [ Passed ]
18:02:28   Info     |   Orphaned tenanted deployments............................... [ Passed ]
18:02:28   Info     |   Orphaned channels........................................... [ Passed ]
18:02:28   Info     |   Orphaned releases........................................... [ Passed ]
18:02:28   Info     |   Only one unfrozen deployment process per project............ [ Passed ]
                    |

garrett.dass · 9 December 2021 18:19

Hi @dalmiro.granas,

Thanks for getting back to me so quickly.

It doesn’t look like you’re missing any indexes from your Integrity check so we can rule that out. Have you noticed any common endpoint or area that is particularly slow or is it just the entire instance in general? It might be worth checking Developer Tools > Network in your browser to narrow down the API calls that are longer running.

It looks like the upgrade cleaned up fragmentation in the DB but it might be worth running this community step template to double-check: SQL - Query Octopus Database for Fragmentation

Please let me know if any of that helps.

Regards,
Garrett

garrett.dass · 9 December 2021 21:25

Hi @dalmiro.granas,

It looks like the way we’re handling MessageBusEvents in v2021.3 sometimes causes issues with performance. We have a query here that might alleviate your issues if you’d like to give this a try:

DELETE 
FROM MessageBusCursor
WHERE LastModified < DATEADD(hour, -1, GETDATE())

WHILE @@ROWCOUNT > 10
BEGIN
	DELETE TOP (1000)
	FROM MessageBusEvent
	WHERE SequenceNumber < (SELECT MIN(SequenceNumber) FROM MessageBusCursor)
END

If this proves to fix your performance issues I’d suggest you set up a runbook to run this query periodically until an update is rolled out that fixes the issue. As always, with any script, please back up and review before running in any production environment.

Looking forward to hearing back.

Regards,
Garrett

dalmiro.granas · 21 December 2021 14:15

Hi Garret!

I’m gonna close this ticket and create a new one dedicated entirely to our performance issues.

Thanks for your help easing our mind checking that upgrade log

Cheers

Edit: lol I cannot close the ticket. Feel free to do it for me!

system · 21 January 2022 14:15

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.