Hi,
we experiencing same issue with 1,4k (Environment, Project) combinations and growing. I rewrote dbo.Dashbord view and added one index to support new view definition. The result is 10 times less time/cpu spent on frequent query “SELECT * FROM dbo.[Dashboard] ORDER BY Id”.
This workaround worked for us on version “Octopus 3.3.4” and maybe something to consider for “the next big release” in addition of fixing Dashboard refreshing feature.
Below is the change script (review it and use it at your own risk):
BEGIN TRAN
DROP VIEW [dbo].[Dashboard]
GO
CREATE VIEW [dbo].[Dashboard_orig]
–WITH SCHEMABINDING
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 dbo.[Deployment] d
INNER JOIN
dbo.[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 dbo.[Deployment] d
INNER JOIN dbo.[ServerTask] t on t.Id = d.TaskId
WHERE
t.State = ‘Success’ AND
d.Id NOT IN (
SELECT Id
FROM (
SELECT
d.Id as Id,
ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId ORDER BY Created DESC) as [Rank]
FROM dbo.[Deployment] d
INNER JOIN
dbo.[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)
) d
INNER JOIN dbo.[ServerTask] t on t.Id = d.TaskId
INNER JOIN dbo.[Release] r on r.Id = d.ReleaseId
WHERE ([Rank]=1 AND CurrentOrPrevious=‘P’) OR ([Rank]=1 AND CurrentOrPrevious=‘C’)
GO
CREATE VIEW [dbo].[Dashboard]
WITH SCHEMABINDING
AS
WITH ep
AS (
SELECT DISTINCT
d.EnvironmentId,
d.ProjectId
FROM dbo.Deployment AS d
),
CurrentDeployment
AS (
SELECT
'C' AS CurrentOrPrevious,
ca.Id,
ca.Created,
ca.TaskId,
ep.EnvironmentId,
ep.ProjectId,
ca.ReleaseId,
ca.[State],
ca.HasPendingInterruptions,
ca.HasWarningsOrErrors,
ca.ErrorMessage,
ca.QueueTime,
ca.CompletedTime
FROM ep
CROSS APPLY (
SELECT TOP 1
d.Id,
d.Created,
d.TaskId,
d.ReleaseId,
t.[State] AS [State],
t.HasPendingInterruptions AS HasPendingInterruptions,
t.HasWarningsOrErrors AS HasWarningsOrErrors,
t.ErrorMessage AS ErrorMessage,
t.QueueTime AS QueueTime,
t.CompletedTime AS CompletedTime
FROM dbo.Deployment AS d
INNER JOIN dbo.[ServerTask] t
ON t.Id = d.TaskId
WHERE NOT ((t.State = 'Canceled'
OR t.State = 'Cancelling')
AND t.StartTime IS NULL)
AND ep.EnvironmentId = d.EnvironmentId
AND ep.ProjectId = d.ProjectId
ORDER BY d.Created DESC
) ca
),
PreviousDeployment
AS (
SELECT
'P' AS CurrentOrPrevious,
d.Id,
d.Created,
d.TaskId,
d.EnvironmentId,
d.ProjectId,
d.ReleaseId,
d.[State],
d.HasPendingInterruptions,
d.HasWarningsOrErrors,
d.ErrorMessage,
d.QueueTime,
d.CompletedTime
FROM CurrentDeployment l
CROSS APPLY (
SELECT TOP 1
dt.Id,
dt.Created,
dt.TaskId,
dt.EnvironmentId,
dt.ProjectId,
dt.ReleaseId,
t.[State] AS [State],
t.HasPendingInterruptions AS HasPendingInterruptions,
t.HasWarningsOrErrors AS HasWarningsOrErrors,
t.ErrorMessage AS ErrorMessage,
t.QueueTime AS QueueTime,
t.CompletedTime AS CompletedTime
FROM dbo.Deployment dt
INNER JOIN dbo.[ServerTask] t
ON t.Id = dt.TaskId
WHERE dt.EnvironmentId = l.EnvironmentId
AND dt.ProjectId = l.ProjectId
AND dt.Created < l.Created
AND t.State = 'Success'
ORDER BY dt.Created DESC
) d
)
SELECT
d.Id,
d.Created,
d.ProjectId,
d.EnvironmentId,
d.ReleaseId,
d.TaskId,
d.CurrentOrPrevious,
d.[State],
d.HasPendingInterruptions,
d.HasWarningsOrErrors,
d.ErrorMessage,
d.QueueTime,
d.CompletedTime,
r.[Version] as [Version]
FROM (
SELECT
Id,
Created,
ProjectId,
EnvironmentId,
ReleaseId,
TaskId,
CurrentOrPrevious,
[State],
HasPendingInterruptions,
HasWarningsOrErrors,
ErrorMessage,
QueueTime,
CompletedTime
FROM CurrentDeployment
UNION ALL
SELECT
Id,
Created,
ProjectId,
EnvironmentId,
ReleaseId,
TaskId,
CurrentOrPrevious,
[State],
HasPendingInterruptions,
HasWarningsOrErrors,
ErrorMessage,
QueueTime,
CompletedTime
FROM PreviousDeployment
) d
INNER JOIN dbo.[Release] r ON r.Id = d.ReleaseId
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Env_Pr_Cre_INCLUDE] ON [dbo].[Deployment]
(
[EnvironmentId] ASC,
[ProjectId] ASC,
[Created] DESC
)
INCLUDE ( [Id],
[TaskId],
[ReleaseId])
GO
COMMIT