Connection Pooling nightmare - Timeout Expired
-
In the 12 years ive been developing software ive never been so stumped on one issue for so long... I took over the design of the companies web application from my boss. I tidied up 75% of the code and now have a pretty tidy way of doing things as follows... each page inherits a page called 'basePage', this has all the necessary functions in it for data manipulation, such as...
GetConnection() as SqlConnection (returns it unopened but populated with the connection string) GetDataTable(sql as string) as DataTable ExecuteQuery(sql as string) as integer etc...Recently we started getting "Timeout Expired" errors "while obtaining a connection from the pool". Ive tried various extremities of parameters in the connection string such as"min pool size=5;max pool size=1000;connect timeout=90;".and have read numerous articles on the subject. I have also turned off connection pooling entirely and still receive the same error (differing only by not mentioning "the pool"). Before you ask - yes, i am usingtry catchblocks in mybasepagefunctions and always closing and disposing my objects in thefinallyblock. Today i received no less than 840 timeout error reports on the server in one hour - there was only 46 people using the application at the most within that hour. The timeouts seem to happen all at once for a few minutes, like a big bottleneck. The server itself was running fine - not even brushing 50% cpu usage. But the web application itself would slow down quite badly, taking up to a minute to serve the page. I set up some tracing and noticed that some pages made no less than 60 calls per page load to theGetConnection()method :wtf: This is due to various business objects being called and checking data etc... Before i even suggest that any of the business model be redesigned, id like to ask.. has anyone ever come across this type of situation before? are most of you spitting coffee on your monitor over the 60 page calls like i nearly did? :sigh: or have you seen this before in big applications with many components? Any help, comments or suggestions are welcomed. Regards, Guy---Guy H (;-)---
-
In the 12 years ive been developing software ive never been so stumped on one issue for so long... I took over the design of the companies web application from my boss. I tidied up 75% of the code and now have a pretty tidy way of doing things as follows... each page inherits a page called 'basePage', this has all the necessary functions in it for data manipulation, such as...
GetConnection() as SqlConnection (returns it unopened but populated with the connection string) GetDataTable(sql as string) as DataTable ExecuteQuery(sql as string) as integer etc...Recently we started getting "Timeout Expired" errors "while obtaining a connection from the pool". Ive tried various extremities of parameters in the connection string such as"min pool size=5;max pool size=1000;connect timeout=90;".and have read numerous articles on the subject. I have also turned off connection pooling entirely and still receive the same error (differing only by not mentioning "the pool"). Before you ask - yes, i am usingtry catchblocks in mybasepagefunctions and always closing and disposing my objects in thefinallyblock. Today i received no less than 840 timeout error reports on the server in one hour - there was only 46 people using the application at the most within that hour. The timeouts seem to happen all at once for a few minutes, like a big bottleneck. The server itself was running fine - not even brushing 50% cpu usage. But the web application itself would slow down quite badly, taking up to a minute to serve the page. I set up some tracing and noticed that some pages made no less than 60 calls per page load to theGetConnection()method :wtf: This is due to various business objects being called and checking data etc... Before i even suggest that any of the business model be redesigned, id like to ask.. has anyone ever come across this type of situation before? are most of you spitting coffee on your monitor over the 60 page calls like i nearly did? :sigh: or have you seen this before in big applications with many components? Any help, comments or suggestions are welcomed. Regards, Guy---Guy H (;-)---
Guy Harwood wrote:
I set up some tracing and noticed that some pages made no less than 60 calls per page load to the GetConnection() method
Is the code closing the connection before getting another?
Guy Harwood wrote:
are most of you spitting coffee on your monitor over the 60 page calls like i nearly did?
You might want to see if you can rationalise some of these calls. For example, if you alwasy call the same three stored procedures in sucession then you might be able to create a wrapper stored procedure that calls the other three so you can only doing one call to the database. You can then iterate over the result sets and pull out multiple sets of data from one database call. (If you are using DataReaders the
NextResult()method is the one that moves to the next result set) The thing that causes the most slow down in an application is roundtrips to the database. If you can reduce the number of roundtrips it would improve performance, and reduce the number of connections you are making. I'll also admit that I've never had to alter the timeout of the connection from the 15 second default. By the way, what edition of SQL Server are you using?
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
Guy Harwood wrote:
I set up some tracing and noticed that some pages made no less than 60 calls per page load to the GetConnection() method
Is the code closing the connection before getting another?
Guy Harwood wrote:
are most of you spitting coffee on your monitor over the 60 page calls like i nearly did?
You might want to see if you can rationalise some of these calls. For example, if you alwasy call the same three stored procedures in sucession then you might be able to create a wrapper stored procedure that calls the other three so you can only doing one call to the database. You can then iterate over the result sets and pull out multiple sets of data from one database call. (If you are using DataReaders the
NextResult()method is the one that moves to the next result set) The thing that causes the most slow down in an application is roundtrips to the database. If you can reduce the number of roundtrips it would improve performance, and reduce the number of connections you are making. I'll also admit that I've never had to alter the timeout of the connection from the 15 second default. By the way, what edition of SQL Server are you using?
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
Hi Colin, Yes the code always closes the connection before getting another. Ive used the
nextresult()stuff before, very handy and will come in useful when i end up knocking down those 60 calls per page load! We are using SQL Server 2000 with SP4 on 2003 server. The server has 3GB ram and 2 Xeon 2.4GHZ processors (effectively 4 cpus according to sql server). I did notice something odd in the event viewer application log earlier that was entered right about the time the errors started pouring in... Error: 15457, Severity: 0, State: 1 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install. :confused: wasnt doing any maintenance and never seen it before.---Guy H (;-)---
-
Hi Colin, Yes the code always closes the connection before getting another. Ive used the
nextresult()stuff before, very handy and will come in useful when i end up knocking down those 60 calls per page load! We are using SQL Server 2000 with SP4 on 2003 server. The server has 3GB ram and 2 Xeon 2.4GHZ processors (effectively 4 cpus according to sql server). I did notice something odd in the event viewer application log earlier that was entered right about the time the errors started pouring in... Error: 15457, Severity: 0, State: 1 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install. :confused: wasnt doing any maintenance and never seen it before.---Guy H (;-)---
Guy Harwood wrote:
Error: 15457,
If you Google “SQL Server 15457” there are quite a number of hits, would seem error code 15457 is an informational error code, but most of the people posing questions about this error code have the same issues you’re experiencing. Most of the issues seem to be related to the db getting locked or query time out values changed. Might want to take a look. http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684322.html[^]
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
-
Guy Harwood wrote:
Error: 15457,
If you Google “SQL Server 15457” there are quite a number of hits, would seem error code 15457 is an informational error code, but most of the people posing questions about this error code have the same issues you’re experiencing. Most of the issues seem to be related to the db getting locked or query time out values changed. Might want to take a look. http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684322.html[^]
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
For some stupid reason i never looked this up :doh: seems a restart solved the problem. gonna look into it more and see what i find. thanks everyone :)
---Guy H (;-)---
-
For some stupid reason i never looked this up :doh: seems a restart solved the problem. gonna look into it more and see what i find. thanks everyone :)
---Guy H (;-)---