Azure SQL Database queries timing out after 15 minutes.

Question

question

MatthewModget-3030 asked Aug 3, '20 Mike-Ubezzi answered Aug 13, '20

Azure SQL Database queries timing out after 15 minutes.

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-logic-apps azure-analysis-services

· 2

5 |1600 characters needed characters left characters exceeded

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

Mike-Ubezzi · Aug 05, 2020 at 06:04 AM

Hi MatthewModget-3030 - I am seeking some clarification on how your services are deployed as there is some nuance between SQL Server on-premise and SQL Component Services that run in the Azure cloud. What is SSAS in the on-premise environment is Azure Analysis Service in the Azure Cloud. It is still supported by SQL Server and that is why you see the Microsoft SQL specific exception but I don't think you actually have a Azure SQL Database in your deployment, and the query is returning a result from your SSAS service. Is this correct? Or is there a remote query to a database instance from Analysis Services? The best way to start investigating this is to use the Azure Analysis Services metrics blade (Azure Portal) to investigate and identify any trends. Use Azure Monitor to get more comprehensive details.

0 ·

Mike-Ubezzi Mike-Ubezzi · Aug 11, 2020 at 04:34 PM

Hi @MatthewModget-3030 - Did you get this issue resolved or do you need further assistance with this?

0 ·

Answer 1 · 2020-08-13T03:14:27.68Z

Mike-Ubezzi answered Aug 13, '20

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.

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.

5 |1600 characters needed characters left characters exceeded

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

question