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?