The timeout period elapsed prior to obtaining a connection from the pool

Our site works fine for 90% of the day, then during our peak hours when traffic is about twice as heavy as normal, everything slows down to a crawl. Page load times that are normally 1 second take 30 seconds. Checking our error logs, it looks like it may be a connection pool issue. We have 3 web servers connected to 1 sql server db. The SQL server is flying under 25% utilization on all cores.

I look at the User Connections counter on our SQL server and see that during our peak we have 400+ User Connections, but off-hours it is around 120+.

I am pretty sure we are just using whatever default settings MS comes with to deal with our app pool. What can I do to test to see if is 开发者_如何学Goan app pool issue? What are the negatives of increasing the app pool size to 1000 (and how do I do this?).

Thanks!

---

In my experience there are 3 primary types of timeouts you can receive from SQL Server:

1) InvalidOperationException - A failure for the client to obtain a pooled connection from its own pool before the timeout specified on the command string (default 15 seconds). The client's pool is at its maximum size, and all pooled connections are in use and stay in use before the timeout elapses.

2) SQLException - Connection Timeout. The client's connection pool is creating a new connection to the database, but the database does not respond before the timeout specified in the command string (default 15 seconds).

3) SQLException - Command Timeout. A connection was obtained, but the time taken for the SQL statement to exercise the command exceeded the timeout specified on the command's CommandTimeout property (default 30 seconds)


Your circumstances of a server performing normally until load is added sounds like case #1. I've found the timeouts come very fast - usually 2 seconds.

I've found the solution to this is to increase the maximum threads in SQL Server. The default is zero - let SQL Server decide. I've seen cases where a stout server sits with little resource use while it has restricted itself by allocating too few threads.

You can increase the max threads setting with this transact-sql:

sp_configure 'max worker threads', 8192
go
Reconfigure 

Then, restart your SQL Service.

BTW, you can see how many threads are currently allocated by SQL Server with this command:

select sum(current_workers_count) from sys.dm_os_schedulers

This threading setting makes an enormous difference in how SQL Server performs under many connections. SQL Server becomes very unresponsive once it runs out of threads.

---

This could be related to sql connections not being properly disposed (returned to the pool). Make sure you are calling SqlConnection.Dispose.

---

This could be because the pool of SQL Connections is exhausted (this is different from the app pool.) You can check that by increasing the pool size through the connection string:

Integrated Security=SSPI;Initial Catalog=northwind;Max Pool Size=100;

But more likely, your database can't keep up with the stream of incoming queries. This causes connections to be waiting for their query to end. Adding more connections will help against burst requests, but not against sustained high traffic.

Here's some suggestions to improve the performance of your SQL Server under sustained high load:

• Throw hardware at the problem (especially RAM on the SQL Server)
• Attach SQL Server Profiler to the server, get a trace of one high-load period, and follow its suggested indexes
• From the trace log, examine long running queries, and improve those together with a T-SQL developer

Good luck, these things can be pretty complex!

---

When I received this error:

The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached

It was due to the fact that I was using the SqlCommand.ExecuteQuery() method instead of SqlCommand.ExecuteNonQuery().

For example: My original stored procdure call look something like this:

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "InsertSproc";
    cmd.Parameters.AddWithValue("@Value", myValue);
    cmd.Parameters.AddWithValue("@LoggedDate", myDate);
    DatabaseManager.instance.ExecuteQuery(cmd);
}

The code above is what threw the exception. However, changing the call to use ExecuteNonQuery() fixed the problem:

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "InsertSproc";
    cmd.Parameters.AddWithValue("@Value", myValue);
    cmd.Parameters.AddWithValue("@LoggedDate", myDate);
    DatabaseManager.instance.ExecuteNonQuery(cmd);
}

---

I had this issue with Powershell and back to back queries (invoke-sqlcmd followed by another invoke-sqlcmd). Both queries involved data modifications. Resolved by adding -connectiontimeout 1 to the parameter call.

Example:

invoke-sqlcmd "insert into testdb..tab1 (cname) select 'x'" -connectiontimeout 1
invoke-sqlcmd "update testdb..tab1 set ctr=ctr+1 where cname='aaa'"

Length of timeout may vary depending on number of rows affected.