Web 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?


"Erin Loy" <erinloy@newsgroups.nospam> wrote in message
news:OG7Hn%234JHHA.3268@TK2MSFTNGP04.phx.gbl...
>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
>
>

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

"Erin Loy" <erinloy@newsgroups.nospam> wrote in message
news:OG7Hn%234JHHA.3268@TK2MSFTNGP04.phx.gbl...
>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
>
>

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Erin Loy" <erinloy@newsgroups.nospam> wrote in message
news:OG7Hn%234JHHA.3268@TK2MSFTNGP04.phx.gbl...
>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
>
>

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" <erinloy@newsgroups.nospam> schreef in bericht
news:OG7Hn%234JHHA.3268@TK2MSFTNGP04.phx.gbl...
>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
>
>

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

"Scott M." <s-mar@nospam.nospam> wrote in message
news:u3wCP45JHHA.1240@TK2MSFTNGP03.phx.gbl...
> Have you done anything to adjust connection pooling settings & have you
> considered using web service caching?
>
>
> "Erin Loy" <erinloy@newsgroups.nospam> wrote in message
> news:OG7Hn%234JHHA.3268@TK2MSFTNGP04.phx.gbl...
>>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
>>
>>
>
>

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

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:Ojv0s77JHHA.3668@TK2MSFTNGP02.phx.gbl...
> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> "Erin Loy" <erinloy@newsgroups.nospam> wrote in message
> news:OG7Hn%234JHHA.3268@TK2MSFTNGP04.phx.gbl...
>>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
>>
>>
>
>

All connections are explicitly closed after use.

-Erin

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:O$awUW9JHHA.780@TK2MSFTNGP03.phx.gbl...
> 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.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Erin Loy" <erinloy@newsgroups.nospam> wrote in message
> news:OG7Hn%234JHHA.3268@TK2MSFTNGP04.phx.gbl...
>>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
>>
>>
>
>

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

"Cor Ligthert [MVP]" <notmyfirstname@planet.nl> wrote in message
news:eyA7CLCKHHA.2028@TK2MSFTNGP03.phx.gbl...
> 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" <erinloy@newsgroups.nospam> schreef in bericht
> news:OG7Hn%234JHHA.3268@TK2MSFTNGP04.phx.gbl...
>>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
>>
>>
>
>

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/art...=Top5WSMistakes



"Erin Loy" <erinloy@newsgroups.nospam> wrote in message
news:epFZdNTKHHA.4376@TK2MSFTNGP03.phx.gbl...
>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
>
> "Scott M." <s-mar@nospam.nospam> wrote in message
> news:u3wCP45JHHA.1240@TK2MSFTNGP03.phx.gbl...
>> Have you done anything to adjust connection pooling settings & have you
>> considered using web service caching?
>>
>>
>> "Erin Loy" <erinloy@newsgroups.nospam> wrote in message
>> news:OG7Hn%234JHHA.3268@TK2MSFTNGP04.phx.gbl...
>>>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
>>>
>>>
>>
>>
>
>