Web Service max out at 20 SQL connections

[Erin Loy]

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

 

[Scott M.]

Have you done anything to adjust connection pooling settings & have you
considered using web service caching?

 

[William \(Bill\) Vaughn]

Are you trying to use MARS? It can exhibit this behavior.

--
____________________________________
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)

 

[Sylvain Lafontaine]

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.

 

[Cor Ligthert [MVP]]

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

 

[Erin Loy]

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

 

[Erin Loy]

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

 

[Erin Loy]

All connections are explicitly closed after use.

-Erin

 

[Erin Loy]

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

 

[Scott M.]

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

 

[Erin Loy]

What performance counters should I be looking at to further diagnose this?

-Erin

 

[JayKon]

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.

 

[kferron]

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.