question

jasejackson-8080 avatar image
0 Votes"
jasejackson-8080 asked ·

SQL Azure Database - Timeout/ Lost Connection

Hi

We have a number of long running SProcs (all have separate SProcs/queries within). The longest approx 50 mins. These are individually executed from logic apps and this way were do not hit the 2 hour azure SQL database timeout issue.

This worked successfully until recently when one of the SProcs simply ends part way through; at or v close to the same step in the SProc. The tables in the very simple Update query (approx 70k records updated) have been rebuilt, indexed and the stats updated but still failure. This is approx 38 mins into the SProc.

Other longer SPriocs work with success. Also at the time of the "drop out" a timeout log is created but this doesn't appear to offer much insight. The overall job has been ran at different times of the day and still fails at this point. When the SProc is executed manually it completes with success.

Can you advise what else we should be checking or validating.

azure-sql-database
· 5
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you identified what is the peace of SQL programming where the timeout comes up? Could you please share with us that piece of code? are you able yo run apart that piece of code and creates a query plan you can share with us?

0 Votes 0 ·

@jasejackson-8080 Can you please post the error message for us to troubleshoot?

0 Votes 0 ·
jasejackson-8080 avatar image jasejackson-8080 KalyanChanumolu-MSFT ·

95052-image.png



Table A is the table being updated.

I do not believe the query is the actual cause of the issue. Although the job fails here 95% of the time it has also failed before this step of the proc or just after. This query and the queries immediately after or before take less than a second to process (updating 50k records on P4 level). Another important point was this was processing successfully for 6 months beforehand.

Leading up to this process there are stages that insert 2 million rows into a table and then update a large number of columns.

The Time Out log has entries with query_hash:-1. Is there a sql limitation and the connection is ended

Manually running the procedure is always successful. The overall parent Store Procedure is executed from a Logic App. Failure/loss of connection occurs around 35+ minutes. Other logic App executions process procedures that run for 60 minutes with success.

0 Votes 0 ·
image.png (44.5 KiB)

When performing bulk operations (millions), updates and inserts on tables involving non clustered indexes, it is recommended that you drop the index first, perform the DML operations and then create the index again.
Please test this approach if it is feasible in your scenario.

0 Votes 0 ·
Show more comments

1 Answer

jasejackson-8080 avatar image
0 Votes"
jasejackson-8080 answered ·

Moved the long running SProcs to ADF and this has proved successful with relatively small increase in financial cost. Not sure I'll find out what using Runbooks did not work successfully as no other changes have been made.

The job is now stable and overall is slightly quicker in execution so off sets the increase in ADF cost in reduced SQL Dbase cost.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.