February 11, 2004 at 4:28 pm
Whenever someone logs on to my website they have to authenticate with a username / password through SQL Server. This is initially very slow (>5 seconds). However subsequent db requests or logins are almost immediate.
I believe my dedicated webserver needs to establish a connection to the SQL server which is initially costly, but then connection pooling kicks in. When I had my website on a shared webserver initial connections were much faster. is that because other poeple's websites on that server and initial connection could have been established earlier or is my new host just slower? Does each website in IIS and each server user have their own connection and pooling?
Could I fix this issue? Can my global.asa have a permanent connection to my website or some script run every minute and connect to the DB? How long is a default timeout to reconnect to a SQl database? Any ideas how I can overcome this costly intitial connection any other way, i.e. changing settings in SQL Server?
Thanks,
Peter
February 12, 2004 at 6:43 am
Not being a web developer I cannot be sure what the issue is. One thing you can check in SQL Server is if the database Auto Close option is on. YOu can see this in Enterprise manager by right-clicking on the DB and selecting properties and going to the options tab or run sp_dboption 'dbname', 'autoclose' in Query Analyzer. Another issue might be that the .Net web page is recompiling at the first run time which will slow it down.
Jack
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 12, 2004 at 11:51 am
Auto-close is not checked. I do not think it is .NET. I have the same page in simple ASP and it is just as slow. And fact is that all of this used to be fast with my last hosting environment, so the code should be OK. Any other settings in SQL Server you can think of?
February 17, 2004 at 11:07 am
Each application may or may not support connection pooling and this is separate from other applications even if the application target the same server and database. Everything in the connection string must be identical for the connection to be pooled. If something is different like a userid, the connection is not pooled. Lots of stuff can be set at the server level that could affect connections - Max concurrent users, query time out, audit level. I believe the default query time out is 600 seconds.
See http://www.sql-server-performance.com/sk_connection_pooling_myths.asp
or http://www.ondotnet.com/pub/a/dotnet/2004/02/09/connpool.html
for more info
Francis
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply