Is more than 100 active connection to SQL server db not normal in an ASP.NET website?

Question

While tracing the active connection on my db i found that some times the connections exceeds 100, is that normal? and after few minutes it return back to 20 or 25 active connection more details about my problem

Traffic on the site is around 200 visitor per day. Why i am asking? because the default MaxPool in the asp.net connection string is 100 Also i am using Connection in the website IIS

Answer 1

That really depends on your site and your traffic. I've seen a site peek out at over 350 active connections to SQL during its peak time. That was for roughly 7,000 concurent web users, on two web servers, plus various backend processes.

Edit

Some additional information that we need to give you a better answer:

• How many Web Processes hit your sql server? For example are you using web gardens? Do you have multiple servers how many if you do? This is important because then you can calculate how many connections you can have by figuring out how many worker threads per process you have configured. Assume worse case, each thread is running which would add a connection to the pool.
• Are you using connection pooling? If so your going to see the connections stick around after the user's request ends. By default its enabled.
• How many concurent users do you have?

But, I think your going after this wrong, your having an issue with no free connections available in your pool. The first thing I'd look for is any leaked connections (connections being held open for longer then they should). For example passing a data reader up to the Web Page, could be a sign of this.

Next thing is to evaluate the default settings. Maybee you should run a web garden which should give you more connections, or increase the number of connections available.

The last thing I would do is try to opitmize queries like in your last question. Let's say you cut those queries in half, all you've done is bought yourself more time until more users come onto the system, and your right back here, only this time you might not be able to optimize that query yet again.

Answer 2

You're leaving out some details making it difficult to answer correctly but...

It depends, really. If you're not using connection pooling then each time a page is hit that requires access to the database a new connection is going to be opened. So sure, it could be perfectly normal.

Answer 3

I would also look into caching. Cache pages, cache query results, etc. You might be surprised how many times you go back to the database to get a list of US States...