Working around query timeouts?

tim.poth's Avatar

tim.poth

29 Nov, 2017 10:40 AM

We are having trouble deleting some step templates due to a query timeout. We have requested our IT group look at database performance but that's been lost in space for 6 weeks. Any suggestions on extending the timeout from the octopus side of things so we can get these objects delete?

Exception occured while executing a reader for `SELECT * FROM dbo.[DeploymentProcess] WHERE ([JSON] LIKE @docid) ORDER BY [Id]` SQL Error -2 - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The wait operation timed out

  1. Support Staff 1 Posted by Reece Walsh on 30 Nov, 2017 04:33 AM

    Reece Walsh's Avatar

    Hi Tim,

    Thanks for getting in touch!

    I'm sorry to hear you are experiencing these issues.

    I've included a link to our Performance documentation here.

    I'd recommend reading through this documentation, in particular, the "Troubleshooting" section to work towards resolving these performance issues. I do understand, however, that you are in a position where you are waiting for your IT Group to review the situation.

    In the interim, I'm going to move this case over to our internal team to see if they have any other thoughts that could help here.

    Thank you for your patience and understanding in this matter, it is greatly appreciated.

    If you require any further assistance or clarification please let me know :)

    Kind Regards,

    Reece

  2. 2 Posted by paul.kelly on 15 Oct, 2018 11:32 AM

    paul.kelly's Avatar

    Hi there,
    have there been any developments on this?
    We are seeing the same problem (current version 2018.6.5; upgrading to 2018.7.14 later this week).
    the problem we're seeing is that the timed-out query has to read all of the data in the table from lob; looking at this direct from SQL Server Management Studio, the query currently takes approximately 95 seconds to complete:
    SELECT *
    FROM dbo.[DeploymentProcess]
    WHERE ([JSON] LIKE @accid)
    ORDER BY [Id]
    There are just short of 10,000 records in the table, but the JSON data is stored in LOB data blocks external to the table structure. No indexing will relieve this situation.
    Being able to alter the default timeout from 60 seconds would get around this temporarily, but ultimately the database needs to be better able to cope with scale as "DeploymentProcess" grows.
    This is easy to replicate for us; just clicking on "LOAD ACCOUNT USAGE" for any account in "Infrastructure\Accounts" results in this time out.
    IO/CPU stats:
    (0 rows affected)
    Table 'DeploymentProcess'. Scan count 1, logical reads 1726, physical reads 0, read-ahead reads 0, lob logical reads 648799, lob physical reads 0, lob read-ahead reads 62889.
     SQL Server Execution Times:
       CPU time = 45359 ms, elapsed time = 163391 ms.

    Any developments on this issue?

    thanks,
    Paul

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac