September 8, 2009 at 9:33 am
On a very heavily used server I'm seeing an awful lot of .NET connections (>1000) . I assumed (always a mistake) that provided the connection string in the .NET apps was the same then SQL would conveniently pool the connections and reuse when needed. So I expected (mistake number 2) to see only a few hundred at most with everyone sharing 10 - 1 sort of ratio these connections.
Can someone point out my (undoubted) stupid error. 😀
September 8, 2009 at 9:55 am
FNS (9/8/2009)
On a very heavily used server I'm seeing an awful lot of .NET connections (>1000) . I assumed (always a mistake) that provided the connection string in the .NET apps was the same then SQL would conveniently pool the connections and reuse when needed. So I expected (mistake number 2) to see only a few hundred at most with everyone sharing 10 - 1 sort of ratio these connections.Can someone point out my (undoubted) stupid error. 😀
A lot of it depends on how the application is written, find out from your applications team to see if the application uses connection pooling, and if the application closes/reuses existing connections or if every data screen/query/dataset requires a brand new connection.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 4:16 am
The application team say yes, they do use pooling.
I don't know if it creates new connections for each data request or reuses them in the code but something is NOT RIGHT, there just aren't that many users hammering away to justify the number of connections I see in EM.
Is there any easy way of testing / proving to the app team that their pooling isn't and it's just opening random connections, leaving them open and sucking all the resources from the SQL box?
September 9, 2009 at 4:50 am
FNS (9/9/2009)
The application team say yes, they do use pooling.I don't know if it creates new connections for each data request or reuses them in the code but something is NOT RIGHT, there just aren't that many users hammering away to justify the number of connections I see in EM.
Is there any easy way of testing / proving to the app team that their pooling isn't and it's just opening random connections, leaving them open and sucking all the resources from the SQL box?
hmmm, different ways of doing it, you can always stick on profiler for a while and give them the trace file, filter it by the application. you can monitor how many connections are made within a time period and see how many login/logout events are occurring.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 6:44 am
hmmm, different ways of doing it, you can always stick on profiler for a while and give them the trace file, filter it by the application. you can monitor how many connections are made within a time period and see how many login/logout events are occurring.
also track the logins/sec counter.. during the time you are running profiler trace. This would give you an idea how many connections are getting opened.
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
September 9, 2009 at 7:42 am
Logins / sec = ~4-500 :blink:
Logouts / sec = 20
User connections climb until they hit some sort of ceiling around 1400 and everything grinds to a halt. Hmmm.
September 9, 2009 at 1:26 pm
i have experienced this, when it happened to me i forgot to close my connections in my code.
Connection1.Close()
It doesnt really help to set the connection pool to unlimited.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply