title: Active Azure Sql Connections are over the connection pool limit
body: <p>We fight the issue in production when once in a while our Azure SQL database performance significantly degrades. We know we have locks on one of the tables, but these locks are not deadlocks, they are long locks and in an hour or so the performance returns to normal. We are trying to find all the possible scenarios on how we get these long locks (every query is super fast and all performance analyzers could show us what causes long locks). The reason for this question is the picture below:</p>
  
  <p><a href="https://i.stack.imgur.com/Ecp6f.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/Ecp6f.jpg" alt="enter image description here"></a></p>
  
  <p>Out connection pool settings allow only 200 connections to be pooled. And most of the times we have about 10-20 open/pooled connections with the database. Then suddenly a number of active connections start to grow and the pool is completely taken. While a number of pooled connections stay below 200, we see a number of active connections using <code>sp_who2</code> reach 1.5k-2k connections (sometimes 4k-5k).</p>
  
  <p>I've built the same chart using Azure Portal monitoring tools. It has different aggregation period but shows the same issue:
  <a href="https://i.stack.imgur.com/Sj9kK.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Sj9kK.png" alt="enter image description here"></a></p>
  
  <p>the connection string we use:</p>
  
  <blockquote>
    <p>Data Source=[server].database.windows.net;initial
    catalog=[database];persist security info=True;user
    id=[user];password=[password];MultipleActiveResultSets=True;Connection
    Timeout=30;Max Pool Size=200;Pooling=True;App=[AppName]</p>
  </blockquote>
  
  <p><strong>How is that possible taking into account connection pool limitation of 200 connections?</strong></p>
  
  <p>ps: there is no periodic task, long running query or other tool doing anything, we checked with <code>sp_who2</code> all the active connections to the database.</p>