question

MatthewModget-3030 avatar image
0 Votes"
MatthewModget-3030 asked Mike-Ubezzi answered

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-databaseazure-logic-appsazure-analysis-services
· 2
5 |1600 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.

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 Votes 0 ·

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

0 Votes 0 ·

1 Answer

Mike-Ubezzi avatar image
0 Votes"
Mike-Ubezzi answered

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.


5 |1600 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.