Sometimes, none of my sql query terminate (even the smallest ones)

Question

I'm facing occasional hanging T-SQL requests on my database since this morning (01/15/2025), even those that usually complete in less than a second.

The solution I found is to kill the running requests in T-SQL via their session ID and wait one or two hours (even if I can't perform select queries of my tables via the Synapse workspace T-SQL editor, I can still perform kill commands).
Another solution is to create a new database from scratch, then i can perform queries while my another database is still hanging, but it's not an ideal solution.

Note that i use Azure Synapse, with a built-in serverless v2 SQL Pool. My tables are External tables.
The "Diagnose and solve problems" tool of Azure indicates "No health related issues found".

Thanks for your solutions!

Answer 1

Hi Thibad -

Based on the information you have provided, it is possible that the issue is related to resource constraints or high concurrency. According to the documentation, the error message "0x80070008" can occur when serverless SQL pool experiences resource constraints, or if there was a transient platform issue.

To troubleshoot this issue, you can try the following steps:

1. Check the query complexity and the amount of data scanned. One serverless SQL pool can concurrently handle 1,000 active sessions that are executing lightweight queries, but the numbers will drop if the queries are more complex or scan a larger amount of data. Try reducing the number of queries that execute simultaneously or the query complexity.
2. Check if the issue is non-transient or related to high concurrency. If the issue is non-transient or you confirmed the problem isn't related to high concurrency or query complexity, create a support ticket.
3. Check if there are any compatibility issues with third-party applications and Azure Synapse Analytics. Sometimes, features and language elements that are commonly used in SQL Server may not be available in Azure Synapse, or they may behave differently.
4. Monitor your dedicated SQL pool workload using DMVs. You can use DMVs to monitor active queries and find top 10 queries longest running queries. From the preceding query results, note the Request ID of the query that you would like to investigate. If none of these steps help, please let me know and I will try to provide further assistance.

Answer 2

Hi Keshta,

Thank you for your quick answer.

I did not specified any error message, my issue is about Synapse that seems down when i try to vizualize my external tables ; screen of Synapse Workspace (the issue exists also in a database administration tool like dbeaver) :

1. I can't check the query complexity because when Synapse seems down for my database I can't even display my external tables so i don't try to perform any queries. But if I try, my query is like "SELECT TOP(1) FROM dbo.my_external_table". Note that when everything works, i can perform "SELECT *" requests and it terminates in less than 1 second.
2. As I said I just try to display my tables, so i don't think the problem is related to high concurrency or query complexity. As you suggest, I agree to create a support ticket, can you send me the link of the ticketing workspace please ? And is it free ?
3. I don't think there is compatibility issues, because sometimes everything works properly during 2 or 3 hours. If there were compatibility problems I would never be able to make my queries work, do we agree?
4. I don't use dedicated SQL pool, I use serveless SQL pool. Thanks for your proposal, I do would like more help in order to setup a stable database environment for my users.

Answer 3

Hi Thibaut - Thanks for the details you provided.

In order to systematically debug this issue, we need to open up a support case. Our support team will look into the backend to address the contention issue.

In order to open up the ticket, logon to Azure portal and click on Help + Support and Click on "Create a Support Request"

Answer 4

Hi Ketsha,

I just saw that i have to subscribe to a support plan if i want to create a ticket.

While waiting to subscribe, are there any alternatives for my purpose (mount a SQL database dynamically connected on my Delta data stored in ADLS2) ? SQL Pool Dedicated, Databricks, Snowflake... ?

Thank you in advance.

Answer 5

Thanks for your response. Dedicated pool does not support Delta today.

While you are waiting, the other best option would be to create the Azure Fabric Capacity and spin up Azure Fabric workspace. Azure Fabric SAAS service can connect to Delta Lake data stored in Azure Data Lake Storage Gen2 (ADLSGEN2)!

You can use the Azure Data Lake Storage Gen2 connector in Fabric to access and manage Delta Lake data.

You can literally spin this up in a matter of minutes and start exploring your data.