The dashboard can sometimes time out when running the SQL query

[flin-8]

Prerequisites

• I have verified the problem exists in the latest version
• I have searched open and closed issues to make sure it isn't already reported
• I have written a descriptive issue title
• I have linked the original source of this report
• I have tagged the issue appropriately (area/*, kind/bug, tag/regression?)

The bug

Sometimes the dashboard times out when executing the SQL query. For an instance that usually takes a few seconds to execute the query, it can spike up past 60s if SQL server gets busy, where a timeout occurs.

What I expected to happen

The query should run quicker, or the timeout should be longer.

Steps to reproduce

1. Go to the home dashboard

Screen capture

Log exerpt

2020-01-28 16:24:22.4312   8668     83  INFO  Executing reader took 60007ms in transaction 'DashboardFactory': 
			    -- Variable setup for when you want to run this query manually
			    -- DECLARE @dataVersion timestamp
			    -- SELECT @dataVersion = DataVersion FROM ServerTask WHERE Id = 
			    -- DROP TABLE #Latest

			    -- This value is equal to the lowest DataVersion that is used by an uncommitted insert/update
			    -- It is guaranteed no record can be inserted/update and have a DataVersion lower than this value
			    -- When there are no open transactions, this value will be larger than the largest DataVersion in the DB
			    SELECT MIN_ACTIVE_ROWVERSION() 

			    DECLARE @ProjectIds as TABLE (ProjectId nvarchar(400))
			    
			    -- Narrowing this criteria any further makes it hard to invalidate the cache
			    INSERT INTO @ProjectIds
			    SELECT DISTINCT ProjectId
			    FROM ServerTask t
                WHERE (t.[SpaceId] = @_SpaceId_0_0)
			        AND t.ProjectId is not null
			    	   AND t.DataVersion >= @dataVersion

			    -- Detect changes to the Release (in case the version number changes)
			    INSERT INTO @ProjectIds
			    SELECT DISTINCT ProjectId
			    FROM Release t
                WHERE (t.[SpaceId] = @_SpaceId_0_0)
			        AND t.DataVersion >= @dataVersion

			    -- Detect the deletion of Projects, Releases and Deployments 
			    INSERT INTO @ProjectIds
			    SELECT DISTINCT ProjectId
			    FROM Event t
                WHERE (t.[SpaceId] = @_SpaceId_0_0)
			        AND t.Category = 'Deleted' 
			        AND t.ProjectId is not null
			    	   AND t.DataVersion >= @dataVersion

			    SELECT ProjectId FROM @ProjectIds

			    DECLARE @cnt int
			    SELECT @cnt = count(*) FROM  @ProjectIds
			    IF @cnt = 0
			    	   RETURN			
			    
			    SELECT * 
			    INTO #Latest
			    FROM (
				    SELECT 
					    'C' AS CurrentOrPrevious, 
					    d.Id as DeploymentId,
					    d.Created,
					    d.ProjectId,
					    d.EnvironmentId,
					    d.ReleaseId,
					    d.TaskId,
					    d.TenantId,
					    d.ChannelId,
						d.SpaceId,
					    t.[State],
					    t.HasPendingInterruptions,
					    t.HasWarningsOrErrors,
					    t.ErrorMessage,
					    t.QueueTime,
					    t.StartTime,
					    t.CompletedTime,
					    r.[Version],
					    ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId, d.TenantId, d.ReleaseId ORDER BY Created DESC) as [Rank]
				    FROM Deployment d
					    INNER JOIN ServerTask t ON t.Id = d .TaskId
					    INNER JOIN [Release] r on r.Id = d.ReleaseId
                    WHERE (t.[SpaceId] = @_SpaceId_0_0)
				        AND NOT ((t.State = 'Canceled' OR t.State = 'Cancelling') AND t.StartTime IS NULL)
					    AND d.ProjectId in (SELECT ProjectId FROM @ProjectIds)
			    ) s
			    WHERE s.Rank = 1
			    
				CREATE INDEX IX_Latest_DeploymentId ON #Latest(DeploymentId)

			    SELECT * 
			    FROM #Latest
			    UNION ALL
				    SELECT *
				    FROM (
					    SELECT 
						    'P' AS CurrentOrPrevious, 
						    d.Id as DeploymentId,
						    d.Created,
						    d.ProjectId,
						    d.EnvironmentId,
						    d.ReleaseId,
						    d.TaskId,
						    d.TenantId,
						    d.ChannelId,
							d.SpaceId,
						    t.[State],
						    t.HasPendingInterruptions,
						    t.HasWarningsOrErrors,
						    t.ErrorMessage,
						    t.QueueTime,
						    t.StartTime,
						    t.CompletedTime,
						    r.[Version],
						    ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId, d.TenantId, d.ReleaseId ORDER BY Created DESC) as [Rank]
					    FROM [Deployment] d
						    INNER JOIN [ServerTask] t on t.Id = d.TaskId
						    INNER JOIN [Release] r on r.Id = d.ReleaseId
					    WHERE (t.[SpaceId] = @_SpaceId_0_0)
						    AND d.Id NOT IN (SELECT DeploymentId FROM #Latest)
						    AND t.State = 'Success'
						    AND d.ProjectId in (SELECT ProjectId FROM @ProjectIds)
				    ) s
				    WHERE s.[Rank] = 1
			    
2020-01-28 16:24:22.4467   8668     83 ERROR  Unhandled error on request: https://octopus.3plearning.com/api/Spaces-1/progression/Projects-1283 a6eb3326366d424f8b17a63c32711c43 by <anonymous> : Exception occurred while executing a reader for `
			    -- Variable setup for when you want to run this query manually
			    -- DECLARE @dataVersion timestamp
			    -- SELECT @dataVersion = DataVersion FROM ServerTask WHERE Id = 
			    -- DROP TABLE #Latest

			    -- This value is equal to the lowest DataVersion that is used by an uncommitted insert/update
			    -- It is guaranteed no record can be inserted/update and have a DataVersion lower than this value
			    -- When there are no open transactions, this value will be larger than the largest DataVersion in the DB
			    SELECT MIN_ACTIVE_ROWVERSION() 

			    DECLARE @ProjectIds as TABLE (ProjectId nvarchar(400))
			    
			    -- Narrowing this criteria any further makes it hard to invalidate the cache
			    INSERT INTO @ProjectIds
			    SELECT DISTINCT ProjectId
			    FROM ServerTask t
                WHERE (t.[SpaceId] = @_SpaceId_0_0)
			        AND t.ProjectId is not null
			    	   AND t.DataVersion >= @dataVersion

			    -- Detect changes to the Release (in case the version number changes)
			    INSERT INTO @ProjectIds
			    SELECT DISTINCT ProjectId
			    FROM Release t
                WHERE (t.[SpaceId] = @_SpaceId_0_0)
			        AND t.DataVersion >= @dataVersion

			    -- Detect the deletion of Projects, Releases and Deployments 
			    INSERT INTO @ProjectIds
			    SELECT DISTINCT ProjectId
			    FROM Event t
                WHERE (t.[SpaceId] = @_SpaceId_0_0)
			        AND t.Category = 'Deleted' 
			        AND t.ProjectId is not null
			    	   AND t.DataVersion >= @dataVersion

			    SELECT ProjectId FROM @ProjectIds

			    DECLARE @cnt int
			    SELECT @cnt = count(*) FROM  @ProjectIds
			    IF @cnt = 0
			    	   RETURN			
			    
			    SELECT * 
			    INTO #Latest
			    FROM (
				    SELECT 
					    'C' AS CurrentOrPrevious, 
					    d.Id as DeploymentId,
					    d.Created,
					    d.ProjectId,
					    d.EnvironmentId,
					    d.ReleaseId,
					    d.TaskId,
					    d.TenantId,
					    d.ChannelId,
						d.SpaceId,
					    t.[State],
					    t.HasPendingInterruptions,
					    t.HasWarningsOrErrors,
					    t.ErrorMessage,
					    t.QueueTime,
					    t.StartTime,
					    t.CompletedTime,
					    r.[Version],
					    ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId, d.TenantId, d.ReleaseId ORDER BY Created DESC) as [Rank]
				    FROM Deployment d
					    INNER JOIN ServerTask t ON t.Id = d .TaskId
					    INNER JOIN [Release] r on r.Id = d.ReleaseId
                    WHERE (t.[SpaceId] = @_SpaceId_0_0)
				        AND NOT ((t.State = 'Canceled' OR t.State = 'Cancelling') AND t.StartTime IS NULL)
					    AND d.ProjectId in (SELECT ProjectId FROM @ProjectIds)
			    ) s
			    WHERE s.Rank = 1
			    
				CREATE INDEX IX_Latest_DeploymentId ON #Latest(DeploymentId)

			    SELECT * 
			    FROM #Latest
			    UNION ALL
				    SELECT *
				    FROM (
					    SELECT 
						    'P' AS CurrentOrPrevious, 
						    d.Id as DeploymentId,
						    d.Created,
						    d.ProjectId,
						    d.EnvironmentId,
						    d.ReleaseId,
						    d.TaskId,
						    d.TenantId,
						    d.ChannelId,
							d.SpaceId,
						    t.[State],
						    t.HasPendingInterruptions,
						    t.HasWarningsOrErrors,
						    t.ErrorMessage,
						    t.QueueTime,
						    t.StartTime,
						    t.CompletedTime,
						    r.[Version],
						    ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId, d.TenantId, d.ReleaseId ORDER BY Created DESC) as [Rank]
					    FROM [Deployment] d
						    INNER JOIN [ServerTask] t on t.Id = d.TaskId
						    INNER JOIN [Release] r on r.Id = d.ReleaseId
					    WHERE (t.[SpaceId] = @_SpaceId_0_0)
						    AND d.Id NOT IN (SELECT DeploymentId FROM #Latest)
						    AND t.State = 'Success'
						    AND d.ProjectId in (SELECT ProjectId FROM @ProjectIds)
				    ) s
				    WHERE s.[Rank] = 1
			    `
System.Exception: Exception occurred while executing a reader for `
			    -- Variable setup for when you want to run this query manually
			    -- DECLARE @dataVersion timestamp
			    -- SELECT @dataVersion = DataVersion FROM ServerTask WHERE Id = 
			    -- DROP TABLE #Latest

			    -- This value is equal to the lowest DataVersion that is used by an uncommitted insert/update
			    -- It is guaranteed no record can be inserted/update and have a DataVersion lower than this value
			    -- When there are no open transactions, this value will be larger than the largest DataVersion in the DB
			    SELECT MIN_ACTIVE_ROWVERSION() 

			    DECLARE @ProjectIds as TABLE (ProjectId nvarchar(400))
			    
			    -- Narrowing this criteria any further makes it hard to invalidate the cache
			    INSERT INTO @ProjectIds
			    SELECT DISTINCT ProjectId
			    FROM ServerTask t
                WHERE (t.[SpaceId] = @_SpaceId_0_0)
			        AND t.ProjectId is not null
			    	   AND t.DataVersion >= @dataVersion

			    -- Detect changes to the Release (in case the version number changes)
			    INSERT INTO @ProjectIds
			    SELECT DISTINCT ProjectId
			    FROM Release t
                WHERE (t.[SpaceId] = @_SpaceId_0_0)
			        AND t.DataVersion >= @dataVersion

			    -- Detect the deletion of Projects, Releases and Deployments 
			    INSERT INTO @ProjectIds
			    SELECT DISTINCT ProjectId
			    FROM Event t
                WHERE (t.[SpaceId] = @_SpaceId_0_0)
			        AND t.Category = 'Deleted' 
			        AND t.ProjectId is not null
			    	   AND t.DataVersion >= @dataVersion

			    SELECT ProjectId FROM @ProjectIds

			    DECLARE @cnt int
			    SELECT @cnt = count(*) FROM  @ProjectIds
			    IF @cnt = 0
			    	   RETURN			
			    
			    SELECT * 
			    INTO #Latest
			    FROM (
				    SELECT 
					    'C' AS CurrentOrPrevious, 
					    d.Id as DeploymentId,
					    d.Created,
					    d.ProjectId,
					    d.EnvironmentId,
					    d.ReleaseId,
					    d.TaskId,
					    d.TenantId,
					    d.ChannelId,
						d.SpaceId,
					    t.[State],
					    t.HasPendingInterruptions,
					    t.HasWarningsOrErrors,
					    t.ErrorMessage,
					    t.QueueTime,
					    t.StartTime,
					    t.CompletedTime,
					    r.[Version],
					    ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId, d.TenantId, d.ReleaseId ORDER BY Created DESC) as [Rank]
				    FROM Deployment d
					    INNER JOIN ServerTask t ON t.Id = d .TaskId
					    INNER JOIN [Release] r on r.Id = d.ReleaseId
                    WHERE (t.[SpaceId] = @_SpaceId_0_0)
				        AND NOT ((t.State = 'Canceled' OR t.State = 'Cancelling') AND t.StartTime IS NULL)
					    AND d.ProjectId in (SELECT ProjectId FROM @ProjectIds)
			    ) s
			    WHERE s.Rank = 1
			    
				CREATE INDEX IX_Latest_DeploymentId ON #Latest(DeploymentId)

			    SELECT * 
			    FROM #Latest
			    UNION ALL
				    SELECT *
				    FROM (
					    SELECT 
						    'P' AS CurrentOrPrevious, 
						    d.Id as DeploymentId,
						    d.Created,
						    d.ProjectId,
						    d.EnvironmentId,
						    d.ReleaseId,
						    d.TaskId,
						    d.TenantId,
						    d.ChannelId,
							d.SpaceId,
						    t.[State],
						    t.HasPendingInterruptions,
						    t.HasWarningsOrErrors,
						    t.ErrorMessage,
						    t.QueueTime,
						    t.StartTime,
						    t.CompletedTime,
						    r.[Version],
						    ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId, d.TenantId, d.ReleaseId ORDER BY Created DESC) as [Rank]
					    FROM [Deployment] d
						    INNER JOIN [ServerTask] t on t.Id = d.TaskId
						    INNER JOIN [Release] r on r.Id = d.ReleaseId
					    WHERE (t.[SpaceId] = @_SpaceId_0_0)
						    AND d.Id NOT IN (SELECT DeploymentId FROM #Latest)
						    AND t.State = 'Success'
						    AND d.ProjectId in (SELECT ProjectId FROM @ProjectIds)
				    ) s
				    WHERE s.[Rank] = 1
			    ` ---> System.Data.SqlClient.SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at Nevermore.Transient.IDbCommandExtensions.<>c__DisplayClass5_0.<ExecuteReaderWithRetry>b__0()
   at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy commandRetryPolicy, RetryPolicy connectionRetryPolicy, String operationName)
   --- End of inner exception stack trace ---
   at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy commandRetryPolicy, RetryPolicy connectionRetryPolicy, String operationName)
   at Nevermore.RelationalTransaction.ExecuteReader(String query, CommandParameterValues args, Action`1 readerCallback, Nullable`1 commandTimeout)
   at Octopus.Core.Features.Dashboard.DashboardFactory.SpaceDashboardFactory.GetUpdates() in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Core\Features\Dashboard\DashboardFactory.cs:line 239
   at Octopus.Core.Features.Dashboard.DashboardFactory.SpaceDashboardFactory.GetInternal() in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Core\Features\Dashboard\DashboardFactory.cs:line 190
   at Octopus.Core.Threading.OperationRequestBatcher`1.Create() in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Core\Threading\OperationRequestBatcher.cs:line 46
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.LazyInitValue()
   at Octopus.Core.Features.Dashboard.DashboardFactory.Get(ISpecificSpacePartition partition) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Core\Features\Dashboard\DashboardFactory.cs:line 33
   at Octopus.Core.Features.Dashboard.Dashboard.Get(Boolean includePreviousSuccessful, Project project)
   at Octopus.Server.Web.Api.Actions.ProgressionViewActionController.GetReleases(Project project, List`1 channels, Channel defaultChannel, IOctopusQueryExecutor queryExecutor) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Web\Api\Actions\ProgressionViewActionController.cs:line 127
   at Octopus.Server.Web.Api.Actions.ProgressionViewActionController.GetReleaseProgression(IOctopusQueryExecutor queryExecutor, String projectId) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Web\Api\Actions\ProgressionViewActionController.cs:line 59
   at Octopus.Server.Web.Api.Actions.ProgressionViewAction.<>c__DisplayClass4_1.<ExecuteRegistered>b__0() in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Web\Api\Actions\ProgressionViewAction.cs:line 30
   at Octopus.Server.Web.Api.Rules.DataVersionCacheRule.Execute(Request request, IOctopusQueryExecutor queryExecutor, Func`1 doExecute) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Web\Api\Rules\DataVersionCacheRule.cs:line 40
   at Octopus.Server.Web.Api.Actions.ProgressionViewAction.ExecuteRegistered(String id)
   at Octopus.Server.Web.Infrastructure.Api.CustomActionWithIdResponder`1.ExecuteRegistered() in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Web\Infrastructure\Api\CustomActionResponder.cs:line 46
   at Octopus.Server.Web.Infrastructure.Api.CustomResponder`1.Respond(TDescriptor options, NancyContext context) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Web\Infrastructure\Api\CustomResponder.cs:line 296
   at Octopus.Server.Web.Infrastructure.OctopusNancyModule.<>c__DisplayClass14_0.<get_Routes>b__1(Object o, CancellationToken x) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Web\Infrastructure\OctopusNancyModule.cs:line 125
   at Nancy.Routing.Route`1.<Invoke>d__7.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Nancy.Routing.DefaultRouteInvoker.<Invoke>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Nancy.Routing.DefaultRequestDispatcher.<Dispatch>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Nancy.NancyEngine.<InvokeRequestLifeCycle>d__22.MoveNext()

Affected versions

Up to Octopus Server 2019.13

Workarounds

Upsize SQL server

Links

https://secure.helpscout.net/conversation/882811030/46146/

[octoreleasebot]

Release Note: Reduce likelihood of dashboard queries timing out

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. If you think you've found a related issue, please contact our support team so we can triage your issue, and make sure it's handled appropriately.