SQL Server Timeout exception for multiple Users using SQL Stress We have a stored procedure which takes around 3-4 secs to execute as it has many joins and unions. But if we try to execute that stored procedure using SQL Stress with 100 threads, it gives many time out exceptions. The message is.. 


  Timeout expired. 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
  . 


This is on a production server.

If we execute the same thing using SQL Stress in our local environment it does not give any exception.

It might be an SQL Server Configuration issue. It shows 5031 MB used in the task manager max and does not go beyond that, however the System memory is 64 GB.

What steps should I take to investigate further?
The timeout is with the connection pool, which is a collection of connections maintained by the client.  All of the connections in the pool are in use, and it is timing out getting a connection object from the connection pool, not timing out trying to connect to the SQL Server.  For example, you may have 30 connections in the connection pool, and a 5 second timeout for obtaining a connection from the pool.  A connection pool timeout will occur if all 30 connections in the pool are busy and none are freed up within 5 seconds of a request for a connection pool object.

So you can increase the number of connections in the pool, increase the connection pool timeout, or work to see if the stored procedure execution time can be improved.  It is likely that all of the connections are busy due to blocking, and if that's the case, increasing the connections in the pool and the timeout won't increase the number of times the stored procedure can execute per minute and you'll need to find out how to make the stored procedure execute faster.  So a good first step would be to look at the execution plans and missing indexes for all of the queries in the stored procedure.

Perhaps obvious, but since you state it works better on the "local" server, what are the configuration differences between the two servers?  If read committed snapshot is enabled on the local server but not the other, that one config change could have a huge impact.