Missing item indexes

[jamesm (James)]

v2019.2.6

Check system integrity returns:

Missing item: IDX dbo.IX_ServerTask_SpaceId NONCLUSTERED 0
Missing item: IDX dbo.IX_ServerTask_SpaceId SpaceId

I have submitted a ticket, but if anyone can let point me in the direction of how to create these indexes, or some other solution, that would be great.

Thanks

JM

[MarkSiedle]

Hi James,

We’ve replied to the issue in your support ticket.

I’m replying here to avoid a duplicate response from our support team. For anyone following, we believe this was due to an external tool (like Azure) making database recommendations to your Octopus database.

Cheers
Mark

[jamesm (James)]

Hi Mark

Thanks for this, it was David, our DBA, who added the indexes, as we were experiencing some performance issues.

I’ve copied him in to this email, in case we want to talk about our performance issues.

Thanks

James

[David_Reed]

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

[MarkSiedle]

Thanks James, Thanks David,

I’ve forwarded these recommendations to our performance team for consideration.

There is also an open issue here you can contribute to in the future (if there are specific recommendations you’d like us to action).

Cheers
Mark