Warehouse does not work, queries run forever without error

[ChristophSDX]

Hello,

since yesterday I cannot retrieve any data from any warehouse. There is no error message. I checked the capacity app and there are CU(s) left. Do you have any idea, it is urgent for my client. 

Inserting data into the warehouse works well.

There are about 400 open sessions, some of them older than 2 days. Unfortunately, I cannot kill them with the KILL command. 

[FabianSchut · Accepted solution]

Can you try to Cancel the running queries from the warehouse and try again? You can Cancel the queries by selecting the three dots from the Warehouse and click on Query activity.

[v-prasare]

@ChristophSDX,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution helps you? or let us know if you need any further assistance here? 

Your feedback is important to us, Looking forward to your response.

Thanks,

Prashanth Are

MS Fabric community support.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

[v-prasare]

@ChristophSDX,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution helps you? or let us know if you need any further assistance here? 

Your feedback is important to us, Looking forward to your response.

Thanks,

Prashanth Are

MS Fabric community support.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

[v-prasare]

Hi @ChristophSDX,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution helps you? or let us know if you need any further assistance here? 

Your feedback is important to us, Looking forward to your response.

Thanks,

Prashanth Are

MS Fabric community support.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

[v-prasare · Accepted solution]

Hi @ChristophSDX,

 Here's are some troubleshooting guide to help you identify and fix the problem: Having 400 open sessions can exhaust resources or cause contention, even if insertion works.

1. Analyze Open Sessions:

Run the following query to inspect all open sessions:

SELECT

    session_id,

    login_name,

    host_name,

    status,

    start_time,

    last_request_start_time,

    last_request_end_time

FROM sys.dm_exec_sessions

WHERE status != 'background';

Focus on sessions that have been idle for long periods (status = 'sleeping'), as they may hold resources unnecessarily.

1. Investigate Blocking Sessions:

Blocked sessions can prevent data retrieval. Use this query to identify blocked and blocking sessions:

SELECT

    blocking_session_id AS Blocker,

    session_id AS Blocked,

    wait_time,

    wait_type,

    resource_description

FROM sys.dm_exec_requests

WHERE blocking_session_id != 0;

Address the blocking sessions first.

Attempt to Kill Sessions: If KILL does not work, the issue might be related to permissions or system constraints. Alternatives:

1. Use Azure Portal or Synapse Studio: Go to Monitor > SQL requests in Synapse Studio. Identify and manually terminate long-running or idle queries.
2. Restart the SQL Pool: If individual session termination isn't possible, restarting the SQL pool will terminate all sessions. This will disconnect all active connections, so coordinate with your team and client:

• In Azure Portal, navigate to your Synapse SQL pool.
• Stop and restart the pool.

Check for Table Locks: Insertion may work while retrieval fails due to locks. Identify locked tables with this query: Resolve locks by addressing the session causing them.

SELECT

    t.name AS TableName,

    l.request_mode,

    l.resource_type

FROM sys.dm_tran_locks l

JOIN sys.tables t ON l.resource_associated_entity_id = t.object_id;

Review Query Performance Settings:

If no blocking or locking issues exist, the issue may be due to resource allocation or query execution limits:

• Adjust Resource Allocation:
• Scale up the Synapse SQL pool in the Azure Portal to increase performance.
• Query Timeout Settings: Ensure your client application or Synapse is not applying a short timeout for queries.

• Logs for Diagnostics:
  Use Synapse diagnostic logs to identify underlying causes of failures:
• Enable and review logs via the Azure Monitor integration.

[FabianSchut · Accepted solution]

Can you try to Cancel the running queries from the warehouse and try again? You can Cancel the queries by selecting the three dots from the Warehouse and click on Query activity.