SQL Serverless Pool with Only Views Experiencing Query Timeouts in Git-Configured Environment

Question

SQL Serverless Pool with Only Views Experiencing Query Timeouts in Git-Configured Environment

Anonymous

Hello Azure Community,

I have an environment configured via Git that includes a SQL Serverless Pool in Azure Synapse Analytics. This pool consists exclusively of views.

Additional Information:

The environment is Git-configured.
I am experiencing timeouts specifically on SELECT statements, some lasting more than 10 minutes.
Currently we have one container with multiple i.e., bronze, silver, gold) Gold being the aggregated data.
This issue occurred for three hours.
The SQL Serverless Pool only contains views; there are no tables.
System query's ran successfully. (i.e. 2+2...)

Error Message: Query:{****}] failed: Query failed because of compute container failures or other transient errors and retry exhausted. This can be an intermittent issue. Please retry later.

Has anyone experienced similar issues? Are there known performance bottlenecks when working with serverless SQL pools and views in a Git-configured environment? Any help would be greatly appreciated!

Update: The problem lasted 3 hours. The error resolved itself after stopping all activity for 30 minutes.

Thank you!

Vinodh247-1375 43,181 Reputation points Volunteer Moderator

2023-09-09T13:56:44.47+00:00
Nicole Inman:

As it is completely serverless there isn't much configurations that we have to do which will be taken care at the backend.

How many mins usually it will take to run this before you started to see this error?

If there are only views, does all the files to which you are calling from a view is present in the location? how big ?
Vinodh247-1375 43,181 Reputation points Volunteer Moderator

2023-09-11T04:26:01.0366667+00:00

Nicole Inman:

Pls confirm if you had a chance to try the suggestion provided for your question.
Anonymous

2023-09-11T19:30:55.42+00:00

Vinodh247:

Thank you for your response.

To answer your questions:

Before encountering this error, the SELECT queries on the views usually took between 5 to 10 minutes to execute before they timed out or returned an error.

Error Message: Query:{****}] failed: Query failed because of compute container failures or other transient errors and retry exhausted. This can be an intermittent issue. Please retry later.

Yes, all the files that the views are calling are present in the same container with different directories. (i.e. Bronze, silver, and gold) bronze has three directories, csv file format, each are less than 5MB, silver is converted to parquet, each directory is less than 1 MB, Gold is partitioned in parquet format, partitioned and less than is 6KB.

It's worth noting that the issue resolved itself after halting all activity for about 30 minutes. However, I'd still like to understand what could have caused this to prevent future occurrences.

Thanks again for your assistance.
Vinodh247-1375 43,181 Reputation points Volunteer Moderator

2023-09-12T05:44:04.7233333+00:00

Nicole Inman: Thanks for the details. Your details proves that there are nothing abnormal in terms of load or the access to the data. In azure sql/MI & synapse there are transient connection errors that happens rarely.

https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-connectivity-issues?view=azuresql

This might be an one-time issue you can raise a support case with microsoft and get it confirmed if the cause was from their end.
Vinodh247-1375 43,181 Reputation points Volunteer Moderator

2023-09-14T15:48:19.62+00:00

Nicole Inman: Please confirm if we are still connected on this question.
PRADEEPCHEEKATLA 91,866 Reputation points

2023-09-18T10:15:59.9966667+00:00

@Anonymous - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Gabe 1

I am having the same issue and it happens every morning.

Create or Alter View
{
    "errorCode": "BadRequest",
    "message": "Operation on target Create View MrpWarehouse failed: Query [Stmt:{8686281C-AD62-4C6D-BBBF-B5BCCED256CA}]_[DQHash:0xE6C5C8BC4E365606]_[Sch:1798e62b-fd92-47ce-b22c-7bfb6a86f4a4]_[Query:{023079DB-AA61-44CA-B89C-3F82AD8C3B0C}] failed:Query failed because of compute container failures or other transient errors, and retry exhausted. This can be an intermittent issue. Please retry later.\r\nError reading external metadata.",
    "failureType": "UserError",
    "target": "Create or Alter View",
    "details": ""
}

PRADEEPCHEEKATLA 91,866 Reputation points

2023-10-26T09:26:18.04+00:00

Gabe - Since this thread is too old, I would recommend creating a new thread on the same forum with as much details about your issue as possible. That would make sure that your issue has better visibility in the community.