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.
- 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.
- 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:
- Use Azure Portal or Synapse Studio: Go to Monitor > SQL requests in Synapse Studio. Identify and manually terminate long-running or idle queries.
- 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.
