Are there any best practices or documentation regarding connection pooling in the connection string between IIS and Microsoft SQL server? I'm often seeing max pool connections reached in the event log and am looking for advice on what to set this value at.
I wouldn't normally expect to change this from its default of 100. Are you getting a high number of errors in the umbraco logs? Maxing out pooled connections is normally an indication that connections are not getting closed or queries taking a long time to run.
Yes, in umbraco.DataLayer.SqlHelper, an exception is being thrown:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
and there are no errors prior to that ?
Connections should normally get closed and reused unless they error and they are not getting closed properly or very long running queries which are keeping the connections open, it is unusual to have that many open connections in the pool.
I have never had the need to increase the default max connections even on very busy websites.
You can increase the number of connections by setting the 'Max Pool Size' on your connection string. I would advise trying to figure out why you are getting so many open connections though in case there is an issue somewhere.
How would I go about finding code that could be causing connections to not be reused? Monitoring of the database server hasn't revealed any long running queries or performance issues there.
Do your IIS logs indicate a large volume of traffic when your connection pool maxes out?
If it does, it would be worth checking what pages are being accessed to determine where to look, also analyse their IP's to determine if it is legitimate traffic or bot flooding
In my case, it ended up being a large volume of traffic attempting to access the autodiscover.xml file by Outlook clients. Since that file does not exist, IIS was responding with a 404. 404s were being redirected to a page in Umbraco. I added a rule in IIS to respond differently to this specific URL and that resolved the issue.
IIS database connection pooling
Are there any best practices or documentation regarding connection pooling in the connection string between IIS and Microsoft SQL server? I'm often seeing max pool connections reached in the event log and am looking for advice on what to set this value at.
I wouldn't normally expect to change this from its default of 100. Are you getting a high number of errors in the umbraco logs? Maxing out pooled connections is normally an indication that connections are not getting closed or queries taking a long time to run.
I normally have around 40 sleeping connections, but I'm frequently maxing out over 100 overnight on the weekends.
Are there any errors in your umbraco log?
Yes, in umbraco.DataLayer.SqlHelper, an exception is being thrown: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
and there are no errors prior to that ? Connections should normally get closed and reused unless they error and they are not getting closed properly or very long running queries which are keeping the connections open, it is unusual to have that many open connections in the pool.
I have never had the need to increase the default max connections even on very busy websites.
You can increase the number of connections by setting the 'Max Pool Size' on your connection string. I would advise trying to figure out why you are getting so many open connections though in case there is an issue somewhere.
This is a good article on connection pooling
https://flylib.com/books/en/3.404.1.120/1/
No, I can't find any other errors in the log.
How would I go about finding code that could be causing connections to not be reused? Monitoring of the database server hasn't revealed any long running queries or performance issues there.
Do your IIS logs indicate a large volume of traffic when your connection pool maxes out? If it does, it would be worth checking what pages are being accessed to determine where to look, also analyse their IP's to determine if it is legitimate traffic or bot flooding
In my case, it ended up being a large volume of traffic attempting to access the autodiscover.xml file by Outlook clients. Since that file does not exist, IIS was responding with a 404. 404s were being redirected to a page in Umbraco. I added a rule in IIS to respond differently to this specific URL and that resolved the issue.
Glad you sorted it out, and thanks for sharing the solution.
is working on a reply...