none
SQL query fails after 1 hour of execution RRS feed

  • Question

  • There are some long running sql queries in our system (to table with about 1,500,000,000 records).

    It starts returning data after a few of seconds.

    But after 1 hour of execution it constantly fails with errors:

    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.
    Msg 0, Level 20, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.

    Or with:

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

    It seems that there is some internal timeout in azure sql.

    I've also tried to run dbcc check for DB or this big table but it also failed with first error.

    Then I've increased Priceing tier from S4: 200 DTUs to S6: 400 DTUs.

    Query completed in 50 minutes. But if I run some additional queries in parallel to make additional workload on DB it fails after one hour of execution.

    There were some other queries which run more then 1 hour but finally they also failed with these errors.

    A few months ago there was no this kind of problem. Seems that MS rolled out some update.

    How can I fix this problem? 

    Wednesday, January 16, 2019 11:46 AM

Answers

  • Hi Serg2016,

    You have a tremendous amount of data. So, first I want to see what you are doing about indexes? And are you doing anything to update statistics after each data load or on a periodic basis?

    Automatic index management in Azure SQL database

    Secondly, it appears you gained some traction by moving from S3 to S6. Have you attempted S7 or S9? As you know, you can scale back down but, did want to communicate two ways of addressing this issue. One is through the blog post with regard to index management and the other is scaling up when high workloads are anticipated.

    Please update this thread with solution or additional questions.

    Regards,

    Mike

    Tuesday, January 29, 2019 10:58 PM
    Moderator

All replies

  • I found this:

    https://stackoverflow.com/questions/23233410/sql-azure-time-consuming-query

    Maybe that helps?

    Wednesday, January 16, 2019 12:16 PM
  • There is no transaction it simple select statement:

    SELECT a.ID, a.Code FROM Table1 a JOIN Table2 b on a.ID = b.ID

    WHERE b.GA IS NOT NULL AND b.GA <> 0

    DBCC CHECK also failed after 1 hour of execution.

    Wednesday, January 16, 2019 12:40 PM
  • It looks like some disconnection  being happened during  the execution

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 16, 2019 1:19 PM
  • Hi Serg2016,

    You have a tremendous amount of data. So, first I want to see what you are doing about indexes? And are you doing anything to update statistics after each data load or on a periodic basis?

    Automatic index management in Azure SQL database

    Secondly, it appears you gained some traction by moving from S3 to S6. Have you attempted S7 or S9? As you know, you can scale back down but, did want to communicate two ways of addressing this issue. One is through the blog post with regard to index management and the other is scaling up when high workloads are anticipated.

    Please update this thread with solution or additional questions.

    Regards,

    Mike

    Tuesday, January 29, 2019 10:58 PM
    Moderator
  • Hi Serg2016,

    Just checking in to see if the above answer helped. If this answers your query, do click “Mark as Answer” and Up-Vote for the same. And, if you have any further query do let us know.

    Thursday, January 31, 2019 4:44 AM
    Moderator
  • Hi,

    I've tried to switch to S9 as Mike suggested and the problem is gone. Then when I've switched back to S4 and now I'm unable to reproduce this error. 

    So seems that switching to S9 and back to S4 have fixed this issue.

    There is no issue with indexes because I've created some synthetic tests like read 1000 records to temp table, clear temp table in infinite loop and it also failed after 1h of execution.

    Is there any way how to troubleshoot this error in future? For standalone sql server there is recomendation to view sql server error logs but i didn't find logs for azure sql.

    Thank you for your help!


    • Edited by Serg2016 Monday, February 11, 2019 9:16 AM
    Monday, February 11, 2019 9:15 AM
  • You should source this document: Monitoring and performance tuning to investigate and tune your Azure SQL PaaS deployment. An on-premise deployment has access to log data but with a PaaS service, you need to use a differant set of tools. The linked document will help with this.

    Regards,

    Mike

    Monday, February 11, 2019 6:24 PM
    Moderator