Creating release results in SQL Timeout Errors

[EM1L (Emil)]

Hi

at the moment we are running self hosted instance with version 2019.6.2 LTS

For quite a while we have been experiencing long page load times of the release page at:
/app#/Spaces-1/projects/project-name/releases
for all frequently used deployment projects.

Our Octopus database is hosted in Azure SQL and Query Performance Insight identifies the following query as the longest running SQL query:

(@projectid_0 nvarchar(12))SELECT *
FROM dbo.[Release_LatestByProjectChannel]
WHERE ((([SpaceId] = 'Spaces-1')))
AND ([ProjectId] = @projectid_0)
ORDER BY [Id]

The following error is straight from the TeamCity build log (last build steps creates a release):

[10:40:29][Octopus Deploy] Creating Octopus Deploy release
[10:40:30][Octopus Deploy] Octopus Deploy Command Line Tool, version 4.43.0
[10:40:30][Octopus Deploy] 
[10:40:30][Octopus Deploy] Detected automation environment: "TeamCity/2018.2.4"
[10:40:30][Octopus Deploy] Handshaking with Octopus Server: https://octopus.*******.uk
[10:40:30][Octopus Deploy] Handshake successful. Octopus version: 2019.6.2; API version: 3.0.0
[10:40:30][Octopus Deploy] Authenticated as: Joe Bloggs <null> 
[10:40:30][Octopus Deploy] This Octopus Server supports channels
[10:40:30][Octopus Deploy] Finding project: My App BFF
[10:40:30][Octopus Deploy] Automatically selecting the best channel for this release...
[10:40:30][Octopus Deploy] Building a release plan for Channel 'Default'...
[10:40:30][Octopus Deploy] Finding deployment process...
[10:40:30][Octopus Deploy] Finding release template...
[10:41:32][Octopus Deploy] Octopus Server returned an error: Exception occurred while executing a reader for `SELECT *
[10:41:32][Octopus Deploy] FROM dbo.[Release_LatestByProjectChannel]
[10:41:32][Octopus Deploy] WHERE ((([SpaceId] = 'Spaces-1')))
[10:41:32][Octopus Deploy] AND ([ProjectId] = @projectid_0)
[10:41:32][Octopus Deploy] ORDER BY [Id]`
[10:41:32][Octopus Deploy] SQL Error -2 - Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
[10:41:32][Octopus Deploy] The wait operation timed out
[10:41:32][Octopus Deploy] Error from Octopus Server (HTTP 500 InternalServerError)
[10:41:32][Octopus Deploy] Exit code: -7
[10:41:32][Octopus Deploy] Octo.exe exit code: -7

I am not sure if this has been an issue, however what is the best corrective action you reccomend?
Would upgrading to the latest LTS version fix this slow query?

Many thanks,
Emil

[Justin_Walsh]

Hi @EM1L!

Sorry to hear that you’re having some issues with your Octopus instance.

The first place I would check would be your Metrics in your Azure portal, and make sure that your compute utilization isn’t maxed out on your database.

Secondly, I’d check your resource utilization on your Octopus server itself, ensuring that there’s sufficient CPU/memory.

If you have the resources available, the next option would be to look at your Octopus Server logs, which can be found in C:\Octopus\Logs directory on a default installation. You might want to consider changing the logging level during your testing to Trace, to get the most information possible.

Feel free to send a trace-level log through to us at support@octopus.com, and we can take a look to see if there’s anything obvious on our end too.

Regarding upgrades: while there haven’t been any major performance changes since 2019.6.2, we’re constantly improving things, so an upgrade definitely can’t hurt.

I hope this helps, and please let us know how you go with the above suggestions.

Best,