SQL Server - System.InvalidOperationException Timeout expired

Hi,

 I am performing load test on my application. I am facing the below issues all of a sudden.

System.InvalidOperationException: 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.

I tried all the solutions available in internet blogs and forums but no luck. I am starting my initial load with 20 users and gradually increasing the concurrency to 100 users. When the load reaches the peak, i am start getting the exception and the numberofconnections is 155 in the database. Please advise on how to proceed.

Regards,
Santhosh

Run SQL Profiler and look for high CPU utilization (Tools menu option in Management Studio).  This will identify stored procedures and batch sql statements that are taking a long time to run.  From there, copy/paste the code that SQL Server executed (shown at the bottom of the profiler screen for each line) and execute it in Query analyzer using the Display Estimated Execution Plan (Query menu after you open a new query window) instead of executing it with the exclamation point toolbar button.

It should give you guidance on improving indexes.

The other thing that could be happening is that you've tried to use a page level or class level SqlConnection object and keeping it open far longer than is needed to encompass many different sql tasks versus opening the connection, executing the query/task/procedure, and closing the connection and releasing it back to the connection pool.

Hi,

My database max pool size is set to 100. But when I run the below query.

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

I see close to 155 connections. can u please clarify.

It is highly unlikely that 155 active connections is causing your performance issues.  That's a pretty small number.
Run profiler as I instructed and it will quickly identify what the issues are.

I have executed the sql profiler and their were no bottle necks found. Each the connection is been reset.

the application is hosted in the amazon cloud environment, and we were performing load test gradually increasing the user count. When i reach the load to 100 users, i start facing this issue.

I was not getting this exception before 15 days, everything was going fine. No builds were moved to affect this scenario. I tried to revert by build to previous workload which executed without any of these issues, but now the issue occurs on that build as well.

We used 40 users and tried to test the application performance manually in the same environment, however still there seems to be timeouts and sql  server i/o and cpu is shooting high. when i tested the application in QA environment with the same load, the number off logs were negligible when compared to amazon environment.

Is there any specific settings in cloud that might have caused this issue. Please suggest.

I didn't realize you were running this in the Amazon Cloud environment.  I have no experience with that.