Hi Mark,
For your info here
are details of the index changes I made in the Octopus DB. I believe it must be the altered one to include some extra columns that has caused the issue.
New Indexes:
CREATE
NONCLUSTERED
INDEX
IX_DeploymentHistory_EnvironmentId_INC_DeploymentId
ON
dbo.DeploymentHistory
(EnvironmentId)
INCLUDE
(DeploymentId)
CREATE
NONCLUSTERED
INDEX
IX_Deployment_ReleaseId_SpaceId_INC_MANY ON
Deployment
(ReleaseId,
SpaceId)
INCLUDE
(Id,
Created,
EnvironmentId,
TaskId,
TenantId)
CREATE
NONCLUSTERED
INDEX
IX_ServerTask_State_ProjectId_EnvironmentId_TenantId_SpaceId_INC_MANY
ON
ServerTask
(State,
ProjectId,
EnvironmentId,
TenantId,
SpaceId)
INCLUDE
(Id,
Name,
Description,
QueueTime,
StartTime,
CompletedTime,
ErrorMessage,
ConcurrencyTag,
HasPendingInterruptions,
HasWarningsOrErrors,
ServerNodeId,
DurationSeconds,
JSON,
DataVersion)
CREATE
INDEX
IX_Event_Category_SpaceId_INC_Id_Occurred ON
Event
(Category,
SpaceId)
INCLUDE
(Id,
Occurred)
CREATE
INDEX
IX_Interruption_SpaceId_INC_DataVersion ON
Interruption
([SpaceId])
INCLUDE
([DataVersion])
Altered Indexes:
DROP
INDEX
IX_ServerTask_SpaceId ON
ServerTask
CREATE
INDEX
IX_ServerTask_SpaceId_INC_Id_State ON
ServerTask
(SpaceId)
INCLUDE
(Id,
State)
I’m afraid I don’t
have the details of the metrics used to determine the indexes any more but there were a few expensive query plans being generated that were executed frequently which triggered us to investigate further.
Thanks,
David
** David
Reed**
DBA
t:
+44 (0) 191 2875819 | e: david.reed@clarity.co.uk
w:
clarity.co.uk
** Clarity
Informatics**
Deltic
House, Kingfisher Way, Silverlink Business Park, Wallsend, Tyne & Wear, NE28 9NX
Important: Terms
and conditions are applicable to the transmission of this email.
Please
visit clarity.co.uk/email-terms