Background
We have our Octopus Server deployed using the Host in Aure Templates, which has created the Database in SQL Azure and the Server running on a VM. Our SQL Azure DB Instance is currently a S1 instance with 10 DTU’s. This has been fine up until we installed 2018.2.5 this week.
Issue
When we try to access an Azure Account through Infrastructure->Accounts->Account, suddenly we are no longer able to load this page as it appears a SQL Query against the VariableSet table times out.
We have checked our VariableSet table and we have ~1300 rows. We have checked the index fragmentation’s on the entire database and rebuilt them all, as well as updating the statistics. None of this has made an difference and we are still getting this issue below.
Exception occured while executing a reader for `SELECT * FROM dbo.[VariableSet] WHERE ([JSON] LIKE @accid) ORDER BY [Id]`
System.Exception
at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy commandRetryPolicy, RetryPolicy connectionRetryPolicy, String operationName)
at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, String operationName)
at Nevermore.RelationalTransaction.Stream
at Octopus.Core.Persistence.Indexes.DeletionDocumentUsageFinder.RelationshipBuilder`2.FindUsages(IRelationalTransaction transaction, Object from, List`1 referencingDocuments)
at Octopus.Core.Persistence.Indexes.DeletionDocumentUsageFinder.FindReferences[TDocument](IRelationalTransaction transaction, TDocument document)
at Octopus.Server.Web.Api.Actions.AccountUsageListAction.ExecuteRegistered(String id)
at Octopus.Server.Web.Infrastructure.Api.Responder`1.Respond(TDescriptor options, NancyContext context)
at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
at Octopus.Server.Web.Infrastructure.OctopusNancyModule.<>c__DisplayClass14_0.<get_Routes>b__1(Object x)
at Nancy.Routing.Route.<>c__DisplayClass4.<Wrap>b__3(Object parameters, CancellationToken context)
--Inner Exception--
SQL Error -2 - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
System.Data.SqlClient.SqlException
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.TrySetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
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)
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)
--Inner Exception--
The wait operation timed out
System.ComponentModel.Win32Exception
I would imagine that this is a bug as this SQL Query is trying to do a string search on a TEXT field, in this case a JSON blob, which cannot possibly be efficient at all?
Temporary Working Solution
To get around the issue, if we scale our SQL DB Instance up from S1 to S3 which gives us 100DTU’s instead of 10DTU’s, then the account page still takes an unacceptable amount of time to load but does eventually load within the 30 second Timeout period. However we are not happy with this temporary solution as this has increased our running costs by 5x, just to be able to open this 1 page.