Error while executing SQL command in transaction 'MachineDomainEventListener': Execution Timeout Expired

[nigel.brown]

We are currently using an on prem version of Octopus Deploy Server V2021.2 (Build 7727) which was recently upgraded.

When we try to add an new tentacle. We are getting the following error.

Error while executing SQL command in transaction 'MachineDomainEventListener': Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. The command being executed was: DELETE FROM [dbo].[EventRelatedDocument] WHERE ([EventId] = @eventid) SQL Error -2 - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. The wait operation timed out.

I notice this post for Octopus Cloud that seems to be a similar issue.

Timeout Error During Deployments and Other Operations

Good Afternoon, I currently run into the following error when deploying projects and when trying to add a new tentacle for a tenant. It doesn’t happen all the time during deploys, but it does happen every time when trying to install a new tentacle on a tenant’s site. The Octopus version is v2021.2 Thank You. Octopus Server returned an error: Error while executing SQL command in transaction ‘MachineDomainEventListener’: Execution Timeout Expired. The timeout period elapsed prior to completi…

Are there any steps we need to perform to fix the issue in our non prem installation?

thank you

[paul.calvert]

Hi Nigel,

Thanks for getting in touch!

Would you be able to run a system integrity check for me and let me know what the results are? We’ve had a couple of instances of indexes going missing that may be the cause here.

To run the system integrity check, head to Tasks, tick the Include System Tasks button and filter for Task Type: System integrity check.

Then select any of the tasks and from the overflow menu at the top right pick Re-run.

If there are any missing indexes it should provide a list. With that, I’ll be able to provide the database scripts to restore them.

Regards,
Paul

[nigel.brown]

Hi Paul, yes there are some errors as listed, below.

Missing item: IDX dbo.IX_Account_DataVersion DataVersion
Missing item: IDX dbo.IX_Account_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Account_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Account_SpaceId SpaceId
Missing item: IDX dbo.IX_ActionTemplate_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_ActionTemplate_SpaceId SpaceId
Missing item: IDX dbo.IX_ActionTemplateVersion_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_ActionTemplateVersion_SpaceId SpaceId
Missing item: IDX dbo.IX_Artifact_DataVersion DataVersion
Missing item: IDX dbo.IX_Artifact_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Artifact_ServerTaskId NONCLUSTERED 0
Missing item: IDX dbo.IX_Artifact_ServerTaskId ServerTaskId
Missing item: IDX dbo.IX_Artifact_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Artifact_SpaceId SpaceId
Missing item: IDX dbo.IX_BuildInformationCreatedDate CreatedDate
Missing item: IDX dbo.IX_BuildInformationCreatedDate NONCLUSTERED 0
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace NONCLUSTERED 0
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace PackageId
Missing item: IDX dbo.IX_BuildInformationPackageIdPerSpace SpaceId
Missing item: IDX dbo.IX_Certificate_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Certificate_SpaceId SpaceId
Missing item: IDX dbo.IX_Channel_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Channel_SpaceId SpaceId
Missing item: IDX dbo.IX_DashboardConfiguration_SpaceId_UserId NONCLUSTERED 0
Missing item: IDX dbo.IX_DashboardConfiguration_SpaceId_UserId SpaceId
Missing item: IDX dbo.IX_DashboardConfiguration_SpaceId_UserId UserId
Missing item: IDX dbo.IX_Deployment_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Deployment_SpaceId SpaceId
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory ChannelId
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory Created
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory DeployedBy
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory EnvironmentId
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory Name
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory NONCLUSTERED 0
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory ProjectId
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory ReleaseId
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory TaskId
Missing item: IDX dbo.IX_Deployment_UpdateDeploymentHistory TenantId
Missing item: IDX dbo.IX_DeploymentEnvironment_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_DeploymentEnvironment_SpaceId SpaceId
Missing item: IDX dbo.IX_DeploymentHistory_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_DeploymentHistory_SpaceId SpaceId
Missing item: IDX dbo.IX_DeploymentProcess_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_DeploymentProcess_SpaceId SpaceId
Missing item: IDX dbo.IX_DeploymentSettings_DataVersion DataVersion
Missing item: IDX dbo.IX_DeploymentSettings_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId CLUSTERED 0
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId ProjectId
Missing item: IDX dbo.IX_DeploymentSettings_SpaceId_ProjectId SpaceId
Missing item: IDX dbo.IX_Event_AutoId_SpaceId AutoId
Missing item: IDX dbo.IX_Event_AutoId_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Event_AutoId_SpaceId SpaceId
Missing item: IDX dbo.IX_Event_DataVersion DataVersion
Missing item: IDX dbo.IX_Event_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Event_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Event_SpaceId SpaceId
Missing item: IDX dbo.IX_Event_UserAgent NONCLUSTERED 0
Missing item: IDX dbo.IX_Event_UserAgent UserAgent
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix EventId
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix NONCLUSTERED 0
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix RelatedDocumentId
Missing item: IDX dbo.IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix RelatedDocumentIdPrefix
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix EventId
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix NONCLUSTERED 0
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix RelatedDocumentId
Missing item: IDX dbo.IX_EventRelatedDocument_RelatedDocumentIdPrefix RelatedDocumentIdPrefix
Missing item: IDX dbo.IX_Feed_DataVersion DataVersion
Missing item: IDX dbo.IX_Feed_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Feed_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Feed_SpaceId SpaceId
Missing item: IDX dbo.IX_Interruption_DataVersion DataVersion
Missing item: IDX dbo.IX_Interruption_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Interruption_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Interruption_SpaceId SpaceId
Missing item: IDX dbo.IX_LibraryVariableSet_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_LibraryVariableSet_SpaceId SpaceId
Missing item: IDX dbo.IX_Lifecycle_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Lifecycle_SpaceId SpaceId
Missing item: IDX dbo.IX_Machine_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Machine_SpaceId SpaceId
Missing item: IDX dbo.IX_MachinePolicy_DataVersion DataVersion
Missing item: IDX dbo.IX_MachinePolicy_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_MachinePolicy_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_MachinePolicy_SpaceId SpaceId
Missing item: IDX dbo.IX_NuGetPackage_PackageId_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_NuGetPackage_PackageId_SpaceId PackageId
Missing item: IDX dbo.IX_NuGetPackage_PackageId_SpaceId SpaceId
Missing item: IDX dbo.IX_NuGetPackage_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_NuGetPackage_SpaceId SpaceId
Missing item: IDX dbo.IX_Project_ClonedFromProjectId ClonedFromProjectId
Missing item: IDX dbo.IX_Project_ClonedFromProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_Project_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Project_SpaceId SpaceId
Missing item: IDX dbo.IX_ProjectGroup_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_ProjectGroup_SpaceId SpaceId
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId RunbookId
Missing item: IDX dbo.IX_ProjectTrigger_RunbookId SpaceId
Missing item: IDX dbo.IX_ProjectTrigger_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_ProjectTrigger_SpaceId SpaceId
Missing item: IDX dbo.IX_Proxy_DataVersion DataVersion
Missing item: IDX dbo.IX_Proxy_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Proxy_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Proxy_SpaceId SpaceId
Missing item: IDX dbo.IX_Release_DataVersion DataVersion
Missing item: IDX dbo.IX_Release_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Release_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Release_SpaceId SpaceId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Assembled
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ChannelId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Id
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled NONCLUSTERED 0
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectDeploymentProcessSnapshotId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled ProjectVariableSetSnapshotId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled SpaceId
Missing item: IDX dbo.IX_Release_SpaceId_ProjectId_ChannelId_Assembled Version
Missing item: IDX dbo.IX_Runbook_DataVersion DataVersion
Missing item: IDX dbo.IX_Runbook_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_DataVersion SpaceId
Missing item: IDX dbo.IX_Runbook_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_ProjectId ProjectId
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId PublishedRunbookSnapshotId
Missing item: IDX dbo.IX_Runbook_Published_PublishedRunbookSnapshotId SpaceId
Missing item: IDX dbo.IX_Runbook_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Runbook_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookProcess_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookProcess_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookProcess_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookProcess_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookRun_Index Created
Missing item: IDX dbo.IX_RunbookRun_Index EnvironmentId
Missing item: IDX dbo.IX_RunbookRun_Index Id
Missing item: IDX dbo.IX_RunbookRun_Index Name
Missing item: IDX dbo.IX_RunbookRun_Index NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_Index ProjectId
Missing item: IDX dbo.IX_RunbookRun_Index RunbookSnapshotId
Missing item: IDX dbo.IX_RunbookRun_Index TaskId
Missing item: IDX dbo.IX_RunbookRun_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookRun_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_RunbookId RunbookId
Missing item: IDX dbo.IX_RunbookRun_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_SpaceId SpaceId
Missing item: IDX dbo.IX_RunbookRun_TenantId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_TenantId TenantId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory Created
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory DeployedBy
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory EnvironmentId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory Name
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory ProjectId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory RunbookId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory RunbookSnapshotId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory TaskId
Missing item: IDX dbo.IX_RunbookRun_UpdateRunbookRunHistory TenantId
Missing item: IDX dbo.IX_RunbookRunHistory_IsPublished IsPublished
Missing item: IDX dbo.IX_RunbookRunHistory_IsPublished NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_Assembled Assembled
Missing item: IDX dbo.IX_RunbookSnapshot_Assembled NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_DataVersion DataVersion
Missing item: IDX dbo.IX_RunbookSnapshot_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_FrozenRunbookProcessId FrozenRunbookProcessId
Missing item: IDX dbo.IX_RunbookSnapshot_FrozenRunbookProcessId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_ProjectId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_ProjectId ProjectId
Missing item: IDX dbo.IX_RunbookSnapshot_RunbookId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_RunbookId RunbookId
Missing item: IDX dbo.IX_RunbookSnapshot_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_RunbookSnapshot_SpaceId SpaceId
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion DataVersion
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_ScopedUserRole_SpaceDataVersion SpaceId
Missing item: IDX dbo.IX_ServerTask_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_ServerTask_SpaceId SpaceId
Missing item: IDX dbo.IX_ServerTask_TaskQueue_QueueTimeState CompletedTime
Missing item: IDX dbo.IX_ServerTask_TaskQueue_QueueTimeState Id
Missing item: IDX dbo.IX_ServerTask_TaskQueue_QueueTimeState NONCLUSTERED 0
Missing item: IDX dbo.IX_ServerTask_TaskQueue_QueueTimeState QueueTime
Missing item: IDX dbo.IX_ServerTask_TaskQueue_QueueTimeState StartTime
Missing item: IDX dbo.IX_ServerTask_TaskQueue_QueueTimeState State
Missing item: IDX dbo.IX_Space_DataVersion DataVersion
Missing item: IDX dbo.IX_Space_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Subscription_DataVersion DataVersion
Missing item: IDX dbo.IX_Subscription_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Subscription_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Subscription_SpaceId SpaceId
Missing item: IDX dbo.IX_TagSet_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_TagSet_SpaceId SpaceId
Missing item: IDX dbo.IX_Team_SpaceDataVersion DataVersion
Missing item: IDX dbo.IX_Team_SpaceDataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_Team_SpaceDataVersion SpaceId
Missing item: IDX dbo.IX_Team_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Team_SpaceId SpaceId
Missing item: IDX dbo.IX_Tenant_ClonedFromTenantId ClonedFromTenantId
Missing item: IDX dbo.IX_Tenant_ClonedFromTenantId NONCLUSTERED 0
Missing item: IDX dbo.IX_Tenant_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Tenant_SpaceId SpaceId
Missing item: IDX dbo.IX_TenantVariable_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_TenantVariable_SpaceId SpaceId
Missing item: IDX dbo.IX_UserRole_DataVersion DataVersion
Missing item: IDX dbo.IX_UserRole_DataVersion NONCLUSTERED 0
Missing item: IDX dbo.IX_VariableSet_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_VariableSet_SpaceId SpaceId
Missing item: IDX dbo.IX_Worker_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_Worker_SpaceId SpaceId
Missing item: IDX dbo.IX_WorkerPool_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_WorkerPool_SpaceId SpaceId
Missing item: IDX dbo.IX_WorkerTaskLease_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_WorkerTaskLease_SpaceId SpaceId

[paul.calvert]

Thanks for checking that.

It is unusual, that list looks to be every single index that is missing.
The incidents we’d seen so far were the same specific 3 or 4 indexes.

I’ll generate the creation scripts for these indexes shortly and post the script here.

[paul.calvert]

Here is the list of all indexes that should exist in the database for your version.
I’ve gone through the list and commented out any of the indexes that don’t appear in your list, as they should already exist in the database.
Due to the number of indexes here, it is possible that I may have incorrectly commented out some that you need or left some in that you already have.

If when you run the script you get an error that index x already exists, you should be able to comment out that particular index and run the script from the next one onwards.
Once the script is complete it would be worth running the system integrity check again and if it flags any remaining missing indexes you should be able to find them in the script and run those in.

We would recommend taking a backup of your database before running this script as a precaution.

Note for anyone else that comes across this topic with missing indexes. This script is intended for version 2021.2.7727 only

CREATE INDEX IX_Account_DataVersion ON Account(DataVersion)
CREATE INDEX IX_Account_SpaceId ON Account(SpaceId)
CREATE INDEX IX_ActionTemplate_SpaceId ON ActionTemplate(SpaceId)
CREATE INDEX IX_ActionTemplateVersion_LatestActionTemplateId ON ActionTemplateVersion(LatestActionTemplateId)
CREATE INDEX IX_ActionTemplateVersion_SpaceId ON ActionTemplateVersion(SpaceId)
--CREATE INDEX IX_Artifact_TenantId ON Artifact(TenantId)
CREATE INDEX IX_Artifact_DataVersion ON Artifact(DataVersion)
CREATE INDEX IX_Artifact_ServerTaskId ON Artifact(ServerTaskId)
CREATE INDEX IX_Artifact_SpaceId ON Artifact(SpaceId)
CREATE INDEX IX_BuildInformationPackageIdPerSpace ON BuildInformation(SpaceId, PackageId)
CREATE INDEX IX_BuildInformationCreatedDate ON BuildInformation(CreatedDate)
--CREATE INDEX IX_Certificate_Thumbprint ON Certificate(Thumbprint)
--CREATE INDEX IX_Certificate_NotAfter ON Certificate(NotAfter)
--CREATE INDEX IX_Certificate_Created ON Certificate(Created)
--CREATE INDEX IX_Certificate_DataVersion ON Certificate(DataVersion)
CREATE INDEX IX_Certificate_SpaceId ON Certificate(SpaceId)
--CREATE INDEX IX_Channel_ProjectId ON Channel(ProjectId)
--CREATE INDEX IX_Channel_DataVersion ON Channel(DataVersion)
CREATE INDEX IX_Channel_SpaceId ON Channel(SpaceId)
CREATE UNIQUE INDEX IX_DashboardConfiguration_SpaceId_UserId ON DashboardConfiguration(SpaceId, UserId)
--CREATE INDEX IX_Deployment_Index ON Deployment(ReleaseId, TaskId, EnvironmentId, Id, ProjectId, Name, Created)
--CREATE INDEX IX_Deployment_TenantId ON Deployment(TenantId)
--CREATE INDEX IX_Deployment_ChannelId ON Deployment(ChannelId)
--CREATE INDEX IX_Deployment_Project ON Deployment(ProjectId, Created, EnvironmentId, ReleaseId, TaskId, ChannelId)
CREATE INDEX IX_Deployment_UpdateDeploymentHistory ON Deployment(TaskId, Name, EnvironmentId, ProjectId, ReleaseId, DeployedBy, TenantId, ChannelId, Created)
CREATE INDEX IX_Deployment_SpaceId ON Deployment(SpaceId)
--CREATE INDEX IX_DeploymentEnvironment_DataVersion ON DeploymentEnvironment(DataVersion)
CREATE INDEX IX_DeploymentEnvironment_SpaceId ON DeploymentEnvironment(SpaceId)
--CREATE INDEX IX_DeploymentHistory_Created ON DeploymentHistory(Created)
CREATE INDEX IX_DeploymentHistory_SpaceId ON DeploymentHistory(SpaceId)
--CREATE INDEX IX_DeploymentProcess_SpaceId ON DeploymentProcess(SpaceId)
--CREATE INDEX IX_DeploymentRelatedMachine_Deployment ON DeploymentRelatedMachine(DeploymentId)
--CREATE INDEX IX_DeploymentRelatedMachine_Machine ON DeploymentRelatedMachine(MachineId)
CREATE UNIQUE CLUSTERED INDEX IX_DeploymentSettings_SpaceId_ProjectId ON DeploymentSettings(ProjectId, SpaceId)
CREATE INDEX IX_DeploymentSettings_DataVersion ON DeploymentSettings(DataVersion)
--CREATE INDEX IX_Event_CommonSearch ON Event(Occurred, AutoId, SpaceId, ProjectId, EnvironmentId, Category, UserId, TenantId, Id, RelatedDocumentIds)
--CREATE INDEX IX_Event_AutoId ON Event(AutoId)
--CREATE INDEX IX_Event_Category_AutoId ON Event(Category, AutoId, Id, RelatedDocumentIds, Occurred)
CREATE INDEX IX_Event_DataVersion ON Event(DataVersion)
CREATE INDEX IX_Event_UserAgent ON Event(UserAgent)
CREATE INDEX IX_Event_SpaceId ON Event(SpaceId)
CREATE INDEX IX_Event_AutoId_SpaceId ON Event(AutoId, SpaceId)
--CREATE INDEX IX_EventRelatedDocument_RelatedDocumentId ON EventRelatedDocument(RelatedDocumentId, EventId)
CREATE INDEX IX_EventRelatedDocument_RelatedDocumentIdPrefix ON EventRelatedDocument(RelatedDocumentIdPrefix, RelatedDocumentId, EventId)
CREATE INDEX IX_EventRelatedDocument_EventId_RelatedDocumentIdPrefix ON EventRelatedDocument(EventId, RelatedDocumentIdPrefix, RelatedDocumentId)
CREATE INDEX IX_Feed_DataVersion ON Feed(DataVersion)
CREATE INDEX IX_Feed_SpaceId ON Feed(SpaceId)
--CREATE INDEX IX_Interruption_TenantId ON Interruption(TenantId)
CREATE INDEX IX_Interruption_DataVersion ON Interruption(DataVersion)
CREATE INDEX IX_Interruption_SpaceId ON Interruption(SpaceId)
CREATE INDEX IX_LibraryVariableSet_SpaceId ON LibraryVariableSet(SpaceId)
--CREATE INDEX IX_Lifecycle_DataVersion ON Lifecycle(DataVersion)
CREATE INDEX IX_Lifecycle_SpaceId ON Lifecycle(SpaceId)
--CREATE INDEX IX_Machine_MachinePolicy ON Machine(MachinePolicyId)
--CREATE INDEX IX_Machine_DataVersion ON Machine(DataVersion)
CREATE INDEX IX_Machine_SpaceId ON Machine(SpaceId)
CREATE INDEX IX_MachinePolicy_DataVersion ON MachinePolicy(DataVersion)
CREATE INDEX IX_MachinePolicy_SpaceId ON MachinePolicy(SpaceId)
CREATE INDEX IX_NuGetPackage_SpaceId ON NuGetPackage(SpaceId)
CREATE INDEX IX_NuGetPackage_PackageId_SpaceId ON NuGetPackage(PackageId, SpaceId)
--CREATE INDEX IX_Project_DiscreteChannelRelease ON Project(Id, DiscreteChannelRelease)
--CREATE INDEX IX_Project_DataVersion ON Project(DataVersion)
CREATE INDEX IX_Project_ClonedFromProjectId ON Project(ClonedFromProjectId)
CREATE INDEX IX_Project_SpaceId ON Project(SpaceId)
--CREATE INDEX IX_ProjectGroup_DataVersion ON ProjectGroup(DataVersion)
CREATE INDEX IX_ProjectGroup_SpaceId ON ProjectGroup(SpaceId)
--CREATE INDEX IX_ProjectTrigger_Project ON ProjectTrigger(ProjectId)
CREATE INDEX IX_ProjectTrigger_SpaceId ON ProjectTrigger(SpaceId)
CREATE INDEX IX_ProjectTrigger_RunbookId ON ProjectTrigger(SpaceId, RunbookId)
CREATE INDEX IX_Proxy_DataVersion ON Proxy(DataVersion)
CREATE INDEX IX_Proxy_SpaceId ON Proxy(SpaceId)
--CREATE CLUSTERED INDEX IX_RelatedDocument_Id ON RelatedDocument(Id)
--CREATE INDEX IX_RelatedDocument_RelatedDocumentId ON RelatedDocument(RelatedDocumentId, Id, Table)
--CREATE INDEX IX_Release_ProjectId_ChannelId_Assembled ON Release(ProjectId, ChannelId, Assembled, Id, Version, ProjectVariableSetSnapshotId, ProjectDeploymentProcessSnapshotId, JSON)
--CREATE INDEX IX_Release_ProjectId_Version_Assembled ON Release(ProjectId, Version, Assembled, Id, ChannelId, ProjectVariableSetSnapshotId, ProjectDeploymentProcessSnapshotId, JSON)
--CREATE INDEX IX_Release_ProjectDeploymentProcessSnapshotId ON Release(ProjectDeploymentProcessSnapshotId)
--CREATE INDEX IX_Release_Assembled ON Release(Assembled, Id)
CREATE INDEX IX_Release_DataVersion ON Release(DataVersion)
CREATE INDEX IX_Release_SpaceId ON Release(SpaceId)
CREATE INDEX IX_Release_SpaceId_ProjectId_ChannelId_Assembled ON Release(SpaceId, ProjectId, ChannelId, Assembled, Id, Version, ProjectVariableSetSnapshotId, ProjectDeploymentProcessSnapshotId)
CREATE INDEX IX_Runbook_ProjectId ON Runbook(ProjectId)
CREATE INDEX IX_Runbook_SpaceId ON Runbook(SpaceId)
CREATE INDEX IX_Runbook_DataVersion ON Runbook(SpaceId, DataVersion)
CREATE INDEX IX_Runbook_Published_PublishedRunbookSnapshotId ON Runbook(SpaceId, PublishedRunbookSnapshotId)
CREATE INDEX IX_RunbookProcess_ProjectId ON RunbookProcess(ProjectId)
CREATE INDEX IX_RunbookProcess_SpaceId ON RunbookProcess(SpaceId)
CREATE INDEX IX_RunbookRun_ProjectId ON RunbookRun(ProjectId)
CREATE INDEX IX_RunbookRun_RunbookId ON RunbookRun(RunbookId)
CREATE INDEX IX_RunbookRun_SpaceId ON RunbookRun(SpaceId)
CREATE INDEX IX_RunbookRun_TenantId ON RunbookRun(TenantId)
CREATE INDEX IX_RunbookRun_Index ON RunbookRun(RunbookSnapshotId, TaskId, EnvironmentId, Id, ProjectId, Name, Created)
CREATE INDEX IX_RunbookRun_UpdateRunbookRunHistory ON RunbookRun(TaskId, Name, EnvironmentId, ProjectId, RunbookId, RunbookSnapshotId, DeployedBy, TenantId, Created)
CREATE INDEX IX_RunbookRunHistory_IsPublished ON RunbookRunHistory(IsPublished)
CREATE INDEX IX_RunbookSnapshot_ProjectId ON RunbookSnapshot(ProjectId)
CREATE INDEX IX_RunbookSnapshot_RunbookId ON RunbookSnapshot(RunbookId)
CREATE INDEX IX_RunbookSnapshot_SpaceId ON RunbookSnapshot(SpaceId)
CREATE INDEX IX_RunbookSnapshot_Assembled ON RunbookSnapshot(Assembled)
CREATE INDEX IX_RunbookSnapshot_DataVersion ON RunbookSnapshot(DataVersion)
CREATE INDEX IX_RunbookSnapshot_FrozenRunbookProcessId ON RunbookSnapshot(FrozenRunbookProcessId)
CREATE INDEX IX_ScopedUserRole_SpaceDataVersion ON ScopedUserRole(SpaceId, DataVersion)
--CREATE INDEX IX_ServerTask_TaskQueue_GetActiveConcurrencyTags ON ServerTask(State, ConcurrencyTag)
--CREATE INDEX IX_ServerTask_TaskQueue_PopTask ON ServerTask(QueueTime, State, ConcurrencyTag, HasPendingInterruptions, ServerNodeId, Name, Description, StartTime, CompletedTime, ErrorMessage, HasWarningsOrErrors, ProjectId, EnvironmentId, TenantId, DurationSeconds, JSON)
--CREATE INDEX IX_ServerTask_Common ON ServerTask(State, Name, ProjectId, EnvironmentId, TenantId, Id, Description, QueueTime, StartTime, CompletedTime, ConcurrencyTag, HasPendingInterruptions, HasWarningsOrErrors, DurationSeconds, ServerNodeId)
--CREATE INDEX IX_ServerTask_ProjectDataVersion ON ServerTask(DataVersion, ProjectId)
CREATE INDEX IX_ServerTask_TaskQueue_QueueTimeState ON ServerTask(QueueTime, Id, State, StartTime, CompletedTime)
CREATE INDEX IX_ServerTask_SpaceId ON ServerTask(SpaceId)
CREATE INDEX IX_Space_DataVersion ON Space(DataVersion)
CREATE INDEX IX_Subscription_SpaceId ON Subscription(SpaceId)
CREATE INDEX IX_Subscription_DataVersion ON Subscription(DataVersion)
--CREATE INDEX IX_TagSet_DataVersion ON TagSet(DataVersion)
CREATE INDEX IX_TagSet_SpaceId ON TagSet(SpaceId)
CREATE INDEX IX_Team_SpaceId ON Team(SpaceId)
CREATE INDEX IX_Team_SpaceDataVersion ON Team(SpaceId, DataVersion)
--CREATE INDEX IX_Tenant_DataVersion ON Tenant(DataVersion)
CREATE INDEX IX_Tenant_SpaceId ON Tenant(SpaceId)
CREATE INDEX IX_Tenant_ClonedFromTenantId ON Tenant(ClonedFromTenantId)
--CREATE INDEX IX_TenantVariable_TenantId ON TenantVariable(TenantId)
CREATE INDEX IX_TenantVariable_SpaceId ON TenantVariable(SpaceId)
--CREATE INDEX IX_User_EmailAddress ON User(EmailAddress)
--CREATE INDEX IX_User_ExternalId ON User(ExternalId)
--CREATE INDEX IX_User_DisplayName ON User(DisplayName)
--CREATE INDEX IX_User_IdentificationToken ON User(IdentificationToken)
CREATE INDEX IX_UserRole_DataVersion ON UserRole(DataVersion)
CREATE INDEX IX_VariableSet_SpaceId ON VariableSet(SpaceId)
--CREATE CLUSTERED INDEX IX_Worker_MachinePolicy ON Worker(MachinePolicyId)
--CREATE INDEX IX_Worker_DataVersion ON Worker(DataVersion)
CREATE INDEX IX_Worker_SpaceId ON Worker(SpaceId)
--CREATE INDEX IX_WorkerPool_DataVersion ON WorkerPool(DataVersion)
CREATE INDEX IX_WorkerPool_SpaceId ON WorkerPool(SpaceId)
CREATE INDEX IX_WorkerTaskLease_SpaceId ON WorkerTaskLease(SpaceId)

[nigel.brown]

thanks Paul, I will run these on Monday and report back to confirm.