asp.net - 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

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.