We are still running Version 3.2.11 which is a relatively older version of octopus deploy. We have plans to upgrade to something greater than 3.3.18 but that is not going to happen very soon. We have noticed that our sql server instance is getting killed in wait time because of the dashboard view requests. Our DBA team actually has tried to make some improvement but it is only marginally helping. We think this comes from both how inefficient the query is as well as the ~5 second UI refresh rate on the endpoint that is essentially ddosing sql server. Have there been any improvements that can help with this or a way to change the UI refresh rate ?
Unfortunately the UI refresh rate is not configurable There have been a change to the dashboard view (introduced in 3.3.14) to improve the performance of the query behind it, I’ve pasted the ALTER script below for you to review, it should be safe to run it on your DB and hopefully see a performance improvement.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[Dashboard] AS
SELECT
d.Id as Id,
d.Created as Created,
d.ProjectId as ProjectId,
d.EnvironmentId as EnvironmentId,
d.ReleaseId as ReleaseId,
d.TaskId as TaskId,
CurrentOrPrevious,
t.[State] as [State],
t.HasPendingInterruptions as HasPendingInterruptions,
t.HasWarningsOrErrors as HasWarningsOrErrors,
t.ErrorMessage as ErrorMessage,
t.QueueTime as QueueTime,
t.CompletedTime as CompletedTime,
r.[Version] as [Version]
FROM (
SELECT
'C' AS CurrentOrPrevious,
d.Id as Id,
d.Created as Created,
d.ProjectId as ProjectId,
d.EnvironmentId as EnvironmentId,
d.ReleaseId as ReleaseId,
d.TaskId as TaskId,
ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId ORDER BY Created DESC) as [Rank]
FROM [Deployment] d
INNER JOIN
[ServerTask] t ON t.Id = d .TaskId
WHERE NOT ((t.State = 'Canceled' OR t.State = 'Cancelling') AND t.StartTime IS NULL)
UNION
SELECT
'P' AS CurrentOrPrevious,
d.Id as Id,
d.Created as Created,
d.ProjectId as ProjectId,
d.EnvironmentId as EnvironmentId,
d.ReleaseId as ReleaseId,
d.TaskId as TaskId,
ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId ORDER BY Created DESC) as [Rank]
FROM [Deployment] d
INNER JOIN [ServerTask] t on t.Id = d.TaskId
LEFT HASH JOIN (
SELECT Id
FROM (
SELECT
d.Id as Id,
ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId ORDER BY Created DESC) as [Rank]
FROM [Deployment] d
INNER JOIN [ServerTask] t ON t.Id = d .TaskId
WHERE NOT ((t.State = 'Canceled' OR t.State = 'Cancelling') AND t.StartTime IS NULL)
) LatestDeployment
WHERE [Rank]=1
) l ON d.Id = l.Id
WHERE
t.State = 'Success' AND
l.Id is null
) d
INNER JOIN [ServerTask] t on t.Id = d.TaskId
INNER JOIN [Release] r on r.Id = d.ReleaseId
WHERE ([Rank]=1 AND CurrentOrPrevious='P') OR ([Rank]=1 AND CurrentOrPrevious='C')
GO
There have been a change to the dashboard view (introduced in