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.
This is the scenario I'm running:
And this is the issue we see:
What I've looked at:
Database calls:
All calls in the database start with:
SqlDatabase database = new SqlDatabase([...]);
And execute either proc with no required output:
return database.ExecuteScalar([...], [...]);
Or read wrapped in a using statement:
using (SqlDataReader reader = (SqlDataReader)database.ExecuteReader([...], [...]))
{
[...]
}
There are no direct uses of SqlConnection, no .Open() or .Close() methods, and no exceptions being thrown
Database verification:
We've run SQL profiler over the login / logout events, and taken snapshots with the sp_who2 command, showing who owns the connections. The latter shows that indeed the web site (seen by machine + credential) are holding the connections.
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.
Connection pool config
I know the min & max size of the connection pool can be altered with the connection string.
E.g.:
"Data Source=[server];Initial Catalog=[x];Integrated Security=SSPI;Max Pool Size=75;Min Pool Size=5;"
A fall back measure may be to set the minimum size to ~10
I understand the default max is 100, and the default min is 0 (from here)
I'm a little bit lithe to think of connection pooling (specific to this setting) and the User Connections performance counter from SQL. This article 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)
I still haven't seen any configuration parameters that are handily defaulting to 5 or 10 minutes, to zero in on...
So, any help is appreciated.
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 & any other factors - and for this question, I am hoping to understand EL scaling its connections up & down.
Thanks.
So, it turns out that SQL user connections are created & added to the pool whenever all other connections are busy. So when long-running queries occur, or the DB is otherwise unresponsive, it will choose to expand to manage the load.
The cause of this in our case happened to be a SQL replication job (unfortunate, but found...) - And the changes in the # of User Connections was just a symptom, not a possible cause.
Although the cause turned out to be elsewhere, I now feel I understand the connection pool management, from this (and assumably other) SQL libraries.