hi, back ground : i am writing an eCommerce application in ASP.Net.(coding in C#) every page has lots of Data Accesses. everything was working fine until i started testing it. i am using a software, which tests load according to the number of users and hits per second that i set. I am using Microsoft SQL server Personal edition--database.
problem : when i increase the number of users from 1 to 10(or more), at once, my number of connections to the database increase tremendously, and surpasses 100(=my max pool size). And then the following error "Timeout expired.The timeout period elapsed prior to obtaining a connection from the pool.This may have occured because all pooled connections were in use and max pool size was reached." Sometime before this, the ASP.Net worker process becomes idle eating 0% CPU time. Now i have checked my code, and most of the places i am using a Data Adapter, which should close the connections on itself. At some places i am using a reader, where i am closing the connections for sure. Also one strange happening, with the sudden burst in the increase in the number of users,the connections(rather the number of connections) increase rapidly, the response time remains the same. and if i am fortunate, and this number does not reach 100, with a steady load, the number of connections come back down to 8-16. Prior to this error, if i check the event log, i get a warning, that SQL Server Personal Edition will not serve more than 8 coincurrent queries at one time, or it may hurt the performance. The problem is, with the same code written in Delphi(not .net), i never get more than 18 connections ever. Also i checked that after disabling the pool, i get no error at all, and though somewhat slow, i get reliable response, and the number of connection never surpass 50-60. can anyone tell me why is this happening,is there some problem with my code? r.
