title: MS Enterprise Library data access - Understanding SQL 'user connections' management
body: <p>I'm trying to understand how MS Enterprise Library's data access block manages its connections to SQL. The issue I have is that under a steady load (from a load test), at 10 minute intervals the number of connections to SQL increases quickly - which causes noticeable jump in page response times from the website.</p>
  
  <p><em>This is the scenario I'm running:</em></p>
  
  <ul>
  <li>Visual Studio load test tools, running against 3 web servers behind a load balancer
  <ul>
  <li>The tools give full visibility over the performance counters to all web + DB boxes</li>
  </ul></li>
  <li>The tests take ~10 seconds each, and perform 4 inserts (form data), and some trivial selects</li>
  <li>There are 60 tests running concurrently. There is no increase or decrease in load during the entire test.
  <ul>
  <li>The test is run for between 20 minutes and 3 hours, with consistent results.</li>
  </ul></li>
  </ul>
  
  <p><em>And this is the issue we see:</em></p>
  
  <ul>
  <li>Exactly every 10 minutes, the performance counter from SQL for <strong>SQL General: User Connections</strong> increases - by ~20 connections total
  <ul>
  <li>The pages performing the HTTP post / DB insert are the ones most significantly affected. The other pages show moderate, but noticeable rises.</li>
  <li>The CPU/memory load on the web servers is unaffected</li>
  </ul></li>
  <li>This increase corresponds with a notable bump in page response times - E.g. from .3 seconds average to up to 5 seconds</li>
  <li>After ~5 minutes it releases many of the connections, with no affect on web performance
  <ul>
  <li>The following 5 minutes of testing gives the same (normal) web performance</li>
  <li>Ultimately, the graph looks like a <a href="http://www.sfu.ca/sonic-studio/handbook/Square_Wave.html" rel="nofollow">square wave</a> </li>
  </ul></li>
  <li>Happens all over again, 10 minutes after the first rise</li>
  </ul>
  
  <hr>
  
  <p><em>What I've looked at:</em></p>
  
  <p><strong>Database calls:</strong></p>
  
  <p>All calls in the database start with:</p>
  
  <pre><code>SqlDatabase database = new SqlDatabase([...]);
  </code></pre>
  
  <p>And execute either proc with no required output:</p>
  
  <pre><code>return database.ExecuteScalar([...], [...]);
  </code></pre>
  
  <p>Or read wrapped in a using statement:</p>
  
  <pre><code>using (SqlDataReader reader = (SqlDataReader)database.ExecuteReader([...], [...]))
  {
      [...]
  }
  </code></pre>
  
  <p>There are no direct uses of SqlConnection, no .Open() or .Close() methods, and no exceptions being thrown</p>
  
  <p><strong>Database verification:</strong></p>
  
  <p>We've run SQL profiler over the login / logout events, and taken snapshots with the <a href="http://sqlserverplanet.com/dba/using-sp_who2" rel="nofollow">sp_who2</a> command, showing who owns the connections. The latter shows that indeed the web site (seen by machine + credential) are holding the connections.</p>
  
  <p>There are no scheduled jobs (DB or web server), and the user connection load is stable when there is no load from the web servers.</p>
  
  <p><strong>Connection pool config</strong></p>
  
  <p>I know the min &amp; max size of the connection pool can be altered with the connection string.</p>
  
  <p>E.g.: </p>
  
  <blockquote>
    <p>"Data Source=[server];Initial Catalog=[x];Integrated Security=SSPI;<strong>Max
    Pool Size=75;Min Pool Size=5;</strong>"</p>
  </blockquote>
  
  <p>A fall back measure may be to set the minimum size to ~10</p>
  
  <p>I understand the default max is 100, and the default min is 0 (<a href="http://www.techrepublic.com/article/educate-yourself-about-net-sql-server-connection-string-syntax/6084879" rel="nofollow">from here</a>)</p>
  
  <p>I'm a little bit lithe to think of connection pooling (specific to this setting) and the User Connections performance counter from SQL. <a href="http://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.100%29.aspx" rel="nofollow">This article</a> introduces these connection pools as being used to manage connection string, which seems different to what I assume it does (hold a pool of connections generally available, to avoid the cost of re-opening them on SQL)</p>
  
  <p>I still haven't seen any configuration parameters that are handily defaulting to 5 or 10 minutes, to zero in on...</p>
  
  <hr>
  
  <p>So, any help is appreciated. </p>
  
  <p>I know that 10 minute spikes sounds like a change in load, or new activity is happening - but we've worked quite hard to isolate those &amp; any other factors - and for this question, I am hoping to understand EL scaling its connections up &amp; down.</p>
  
  <p>Thanks.</p>