Dashboard View is killing sql server (3.2.11)

Its been a long time since our last issue,

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 ?

Thanks,

Brent

Hi Brent,

Unfortunately the UI refresh rate is not configurable :frowning: 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

I hope that helps!

Thank you and warm regards,
Henrik