SQL Issues 


Certain SQL queries were taking so long to run that the web server would timeout the the page before the query had completed. What made this even weirder was the fact that I had set the timeout in the code to 1.5 minutes knowing the actual web page would timeout in 2 minutes. So technically the pages should have crashed and caught in my exception handling before the web server timed out the page. What made this an even bigger mystery was the fact that if I ran the exact same query that was timing out in Query Analyzer it would return in 14 seconds.

...The timeouts I was starting to see were not the queries timing out (which would have caused the exceptions I was expecting) but instead were the database connections timing out. This was masked a little bit because the connection pooling that .NET provides made the Open call to the database happen instantly even when there were no user connections left on the SQL server. The connection pool doesn't try and grab a connection from the pool until you actually run a query.

Now what is totally amazing to me is this meant that all we have been running two dating sites, forums, plus three other sites that are all database driven and they were executing perfectly for an entire year using only five connections to the database server. 

This past week we ran into a weird problem on the SpecialtyMatch dating sites. Certain SQL queries were taking so long to run that the web server would timeout the the page before the query had completed. What made this even weirder was the fact that I had set the timeout in the code to 1.5 minutes knowing the actual web page would timeout in 2 minutes. So technically the pages should have crashed and caught in my exception handling before the web server timed out the page. What made this an even bigger mystery was the fact that if I ran the exact same query that was timing out in Query Analyzer it would return in 14 seconds. Run the same query on the website and it would timeout.

At this point I started running lots of logging and performance monitoring on the SQL server trying to figure out this behavior. What I finally found out made total sense and at the same time shocked me. I had the SQL server configured to only allow 5 simultaneous user connections. The timeouts I was starting to see were not the queries timing out (which would have caused the exceptions I was expecting) but instead were the database connections timing out. This was masked a little bit because the connection pooling that .NET provides made the Open call to the database happen instantly even when there were no user connections left on the SQL server. The connection pool doesn't try and grab a connection from the pool until you actually run a query.

Now what is totally amazing to me is this meant that all we have been running two dating sites, forums, plus three other sites that are all database driven and they were executing perfectly for an entire year using only five connections to the database server. At some points we have had over 180 users on the dating sites. It just amazes me that all of that ran with everyone in a queue to use five connections. Just goes to show you that the connection pooling in .NET works very well . 

Posted: Sun - January 16, 2005 at 11:35 AM          

©