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

Question

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 an app pool issue? What are the negatives of increasing the app pool size to 1000 (and how do I do this?).

Thanks!

Answer 1

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

Answer 2

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!

Answer 3

Not sure how to comment, but this was my question... really appreciate the responses.

We are using SQL Server 2005 and MS Windows 2003 and ASP.NET 2.0.

Our database has very little load on it. CPU doesn't go over 30% on any of the 8-cores. We do a lot of caching using memcached, so it doesn't seem to hit our server very hard. But we still have over 400+ connections to our db server during our peak. So I am at a loss.

Could it be that the server isn't just being hit very hard but there are lots of small queries coming and and connections to the db are opening faster than they are closing which is creating a bottleneck on the web server?

Thanks, Aaron