Azure SQL Database queries timing out after 15 minutes.

Matthew Modget 26 Reputation points
2020-08-03T11:04:29.943+00:00

I have several Logic Apps and SSAS partitions that execute queries which take longer than 15 minutes to run. As of this morning a lot of them have started to timeout at around the 10 - 15 minute mark and I have no idea why. Here's the error message that they're all reporting:

Error returned: 'OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding..

Other than this the database is working fine.

Azure SQL Database
Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
439 questions
Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
2,873 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mike Ubezzi 2,776 Reputation points
    2020-08-13T03:14:27.68+00:00

    Want to provide information that details an approach to investigating this scenario. Please see: Using xEvents to monitor Azure Analysis Services (Related: Using xEvents to monitor Azure Analysis Services).

    You can use SQL Server Management Studio (SSMS) to configure a xEvents for Azure Analysis Services. Today, you can only configure Azure Analysis services to log to a stream or ring buffer and not to a file. In some cases, you may want to log events for offline analysis or to retain historically. We have provided an example of using the Tabular Object Modeling APIs to create an xEvents session and logging the data to disk and a richer sample to trace to a database with a windows service. The xEvents Logging for Azure Analysis Services sample and ASTrace samples are available on GitHub at https://github.com/Microsoft/Analysis-Services.

    The easiest way to use this sample is to use SSMS to configure streaming xEvents to see which events you would like to log. First, create an xEvents session in SSMS. Then pick which events you like to record and set the data mode to streaming. Run some queries or do other operations, and then look at the xEvents in the “Watch Live Data” option on the trace session in SSMS to verify the data. If these events are the ones you want, then you can script these out to a file.

    79a3826d-e698-4328-a83d-fbab3444363b.png

    The sample program takes the TMSL script file to define the events it will record. Then you can run the sample program to create a new session, and it will trace these events to a file. Be sure to install the latest Azure Analysis Services client libraries to ensure you have support to integrated authentication.

    If Azure Analysis Services is sitting over Azure SQL Database or Azure Synapse Analytics (Data Warehouse), then the query could be timing out in attempting to return a dataset from either of these data sources. If this is the case, leverage: Database Advisor performance recommendations for Azure SQL Database and Synapse SQL recommendations to ensure the data source(s) is optimized for overall solution efficiency.

    0 comments