 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / ADO.NET / December 2006Web Service max out at 20 SQL connections
I have been tasked with load testing our .NET 2.0 web service, which is really just wraps 1 or 2 very simple and light weight SQL queries. Under light load (less than 20 requests per second), everything works fine. Each transaction takes about 10-12 ms to complete. As the load increases (anything more than 20 web requests per second), transactions begin to take *much* longer (more than 10 seconds or more). 20 SQL Server connections seems to be a hard limit of some kind, not a performance/hardware limitation, but the only connection limitation setting I can find in SQL Server 2005 is "Maximum number of concurrent connections" and is set to 0 (Unlimited connections). After investigating further, it looks to me like the number of active connections to SQL Server never rise above 20 connections (according to the User Connections performance counter), and web requests are just sitting idle and waiting for connections to become available. We are using thread pooling and closing connections appropriately. My configuration is: 1 server SQL Server 2005 (Standard version from MSDN subscription) ASP.NET 2.0 I could really use help on this one. Thanks, -Erin Have you done anything to adjust connection pooling settings & have you considered using web service caching? >I have been tasked with load testing our .NET 2.0 web service, which is >really just wraps 1 or 2 very simple and light weight SQL queries. Under [quoted text clipped - 30 lines] > > -Erin I have tried a lot of things over the last few days. Adjusting connection pooling (Min 50, Max 100) didn't seem to change performance. Please point me to web service caching info. Does this make sense when all requests will contain different data? (the servce is for toolbars to query info about URLs as users surf the Internet). -Erin > Have you done anything to adjust connection pooling settings & have you > considered using web service caching? [quoted text clipped - 33 lines] >> >> -Erin Caching will help when the service might return the same results given the same input. The following article discusses caching in more detail: http://www.theserverside.net/tt/articles/showarticle.tss?id=Top5WSMistakes >I have tried a lot of things over the last few days. Adjusting connection >pooling (Min 50, Max 100) didn't seem to change performance. [quoted text clipped - 42 lines] >>> >>> -Erin Are you trying to use MARS? It can exhibit this behavior.  Signature____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- >I have been tasked with load testing our .NET 2.0 web service, which is >really just wraps 1 or 2 very simple and light weight SQL queries. Under [quoted text clipped - 30 lines] > > -Erin I did not initially use MARS, but enabled it two days ago when trying to troubleshoot the problem. I increased performance substantially. Physical connections stayed the same, but Logical connections (based on the Windows performance conters) jumped up past 100. I was able to service a few more requests per second, but still not enough. -Erin > Are you trying to use MARS? It can exhibit this behavior. > [quoted text clipped - 32 lines] >> >> -Erin Another possibility (but I'm not sure of this one) is that you don't explicitely close the opened connections; hence waiting for the garbage collector to call the dispose method of the Connection objects sometime after they have got out of scope. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) >I have been tasked with load testing our .NET 2.0 web service, which is >really just wraps 1 or 2 very simple and light weight SQL queries. Under [quoted text clipped - 30 lines] > > -Erin All connections are explicitly closed after use. -Erin > Another possibility (but I'm not sure of this one) is that you don't > explicitely close the opened connections; hence waiting for the garbage [quoted text clipped - 35 lines] >> >> -Erin Erin, Why so many connections, you have one program normaly handling the requests from one user one by one in a sequential way. Why so many connection for one user. I certainly would not set it to zero, as it is very clear told not to do that on MSDN. Cor >I have been tasked with load testing our .NET 2.0 web service, which is >really just wraps 1 or 2 very simple and light weight SQL queries. Under [quoted text clipped - 30 lines] > > -Erin The service serves data about URLs to toolbars as users browse the Internet. It needs to be able to handle thousands of simultanious connections. SQL Server installs by default with a "0" connection limit. Please point me to info on appropriately setting this value. -Erin > Erin, > [quoted text clipped - 40 lines] >> >> -Erin What performance counters should I be looking at to further diagnose this? -Erin >I have been tasked with load testing our .NET 2.0 web service, which is >really just wraps 1 or 2 very simple and light weight SQL queries. Under [quoted text clipped - 30 lines] > > -Erin How many web servers are feeding queries in? More than 1? Remove 1 web server (or add one) and see if the number of concurent connections changes. In an IIS->ODBC->Informix setup I found that pooling was going on in multiple places. IIS was doing it, ODBC, ADO (was never clear on the ADO) and Informix. It wasn't until all of the configurations were setup perfectly that we got the maximum throughput through all 12 webservers and the database. However, I isolated it to settings on the web server by adding and removing them. > I have been tasked with load testing our .NET 2.0 web service, which is > really just wraps 1 or 2 very simple and light weight SQL queries. Under [quoted text clipped - 30 lines] > > -Erin The red flag that comes to mind is that in your original post you said "We are using Thread Pooling" Thread pooling and connection pooling are very different things.. are you opening connections on a worker thread? Its possible im off base, and that you meant connection pooling, but i find it interesting that youre getting a hard limit at 20. With a ThreadPool max of 25 threads per processor, add in a bounding bug and you might be bumping against this threshold. > How many web servers are feeding queries in? More than 1? Remove 1 web server > (or add one) and see if the number of concurent connections changes. [quoted text clipped - 40 lines] > > > > -Erin Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.