How can I diagnose intermittent connection pool exhaustion in a .NET app using ADO.NET with SQL Server on Glitch?

Answer the core question first

What metrics or events prove a pool is exhausted because of a leak versus simply hitting the Max Pool Size?

• SqlConnectionPoolStatistics (or ConnectionPoolStatistics API) exposes:
  • ActiveConnectionCount – connections currently in use.
  • IdleConnectionCount – connections ready to be reused.
  • TotalConnectionCount – sum of active and idle.
  • MaxPoolSize – the limit set in the connection string.
  • ConnectionWaitTime – average wait when the pool is full.
• SQL Server extended events: sqlserver.connection_start and sqlserver.connection_end give a timeline of connections being opened and closed. A spike in connection_start without matching connection_end indicates a leak.
• Perf counters: SQL Server: Connections (Total, In-Use, Idle) and .NET CLR: Timer (for connection‑pool timers) can be read via PerformanceCounter or Get-Counter in PowerShell.

How to differentiate a non‑returned connection from a legitimate queue wait when the pool limit is active?

• If ActiveConnectionCount equals MaxPoolSize and ConnectionWaitTime is >0, the pool is full and callers are queued – this is the hard limit scenario.
• If ActiveConnectionCount is MaxPoolSize but IdleConnectionCount is 0 and the number of open connections in the application remains high over time, you likely have a leak.
• Cross‑check with extended events: a long‑running connection_start that never ends signals a leak; many short connection_start/connection_end pairs that pile up signals a queue.

Practical diagnostic steps for your Glitch deployment

1. Enable pool statistics in code (run once during a maintenance window):

   using System.Data.SqlClient;
   var stats = SqlConnectionPoolStatistics.GetAll();
   foreach (var s in stats) {
       Console.WriteLine($"Pool {s.PoolName}: Active={s.ActiveConnectionCount}, Idle={s.IdleConnectionCount}, Total={s.TotalConnectionCount}, Max={s.MaxPoolSize}");
   }
   

2. Log these values every minute (or use a lightweight scheduler) and store them in a file or a simple in‑memory list for later analysis.
3. Set up a short extended‑events session on the SQL Server instance (if you have access via the Glitch console or a remote query tool):

   CREATE EVENT SESSION PoolTrace ON SERVER
   ADD EVENT sqlserver.connection_start,
   ADD EVENT sqlserver.connection_end
   ADD TARGET package0.event_file(FILENAME='C:\PoolTrace.xel')
   WITH (MAX_MEMORY=4096, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=ALL, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF);
   ALTER EVENT SESSION PoolTrace ON SERVER STATE = START;
   

4. During a period when you see time‑outs, stop the session and examine the file with sys.fn_xe_file_target_read_file to match start/end pairs.
5. If you notice many connection_start events without corresponding connection_end events, you have a leak. If the ratio of start to end is high but the pool never reaches MaxPoolSize, the issue is likely long‑running queries or blocking.
6. To isolate the root cause without restarting Glitch, temporarily set Pooling=false on a test connection string and observe if the application still fails. If the failures disappear, the problem is indeed the pool; if not, the issue is deeper (e.g., query timeouts, network hiccups).
7. As a quick sanity check, run SqlConnection.ClearPool(connection) on a known open connection and verify that IdleConnectionCount drops to zero. This confirms that the API is reporting correctly.

What to look for in the logs

• Repeated Timeout expired or Unable to connect messages when the pool is full.
• Large ConnectionWaitTime spikes in the statistics dump.
• Extended‑events showing connection_start events that persist for minutes without a matching end.

Ask for a missing diagnostic detail

To refine the recommendation, could you confirm whether you have access to Glitch’s container memory/CPU usage metrics during the timeout windows? If the host is hitting its memory quota, the SQL Server instance may reject new connections even when the pool has capacity.