No.Of users per second exceding

Question

No.Of users per second exceding

AAkula

SSCertifiable

Points: 5394
More actions
December 22, 2008 at 11:51 am

#204453

Hi,
we are using spot light to monitor SQL Server 2005 EE x64 on windows 2003 EE x64.
Spot light giving the below alarm:
User Login Rate alarm
The User Login Rate alarm becomes active when the number of logins per second exceeds a threshold. This can be an indicator of poor application design. Creating a connection to SQL Server is relatively expensive, and coding practices where an application repeatedly connects and disconnects from SQL Server should be avoided.
While reconnecting will not necessarily slow down all users of the SQL Server, it will often result in poor performance for the application that is re-connecting all the time.
I attached an excel sheet what are the sessions connected to SQL Server. Could you plz tell me what can we do to improve the performance.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Change the application to use connection pooling. It isn't a SQL Server problem.

AAkula SSCertifiable Points: 5394 More actions · Answer 2

thanks,

the sharepoint application databases are sitting in the SQL Server. So where can I use this Connection pooling?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

You are seeing the nature of Sharepoint, you can't change how it behaves except as prescribed by Microsoft in KB articles and supporting documentation. Is there a specific problem you are having or an error? My Sharepoint database server runs in the 400-500 connection range, and they reset really fast. It is just how sharepoint works, you can't change it, and it generally isn't a problem.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

kotlas7 (12/22/2008)
Could you plz tell me what can we do to improve the performance.

Frankly I am not seeing any evidence that you have a performance problem. The peformance metric being used here strikes me as extremely flaky and diagnostic at best. As such, I would ignore it unless it was connected to some real perceivable performance problem.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

RBarryYoung (12/22/2008)
kotlas7 (12/22/2008)
Could you plz tell me what can we do to improve the performance.
Frankly I am not seeing any evidence that you have a performance problem. The peformance metric being used here strikes me as extremely flaky and diagnostic at best. As such, I would ignore it unless it was connected to some real perceivable performance problem.

It's not a SQL Server target metric, but more of an overall application architecture metric. Consistent login/logout could be a sign of performance problems in the application, not in SQL Server. The app is what incurs the cost to connect repeatedly. Enabling connection pooling could help, but MOSS/Sharepoint already uses it, it just does a whole lot of activity.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Oh, I know what it is Jonathan, but it's still flaky.

Threshold alarms built on performance metrics that are entirely situational, and even then need to be volume-scaled are of very little value generally. Their hallmark is "This might be a problem..." type alerts and they are the kind of chaff that performance product companies use to fill-up their inference engine to make it look like it's actually doing something.

In fact, the vast majority of performance threshold alarms are virtually meaningless unless they have been set for your specific environment, application and situation.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

RBarryYoung (12/22/2008)
Oh, I know what it is Jonathan, but it's still flaky.
Threshold alarms built on performance metrics that are entirely situational, and even then need to be volume-scaled are of very little value generally. Their hallmark is "This might be a problem..." type alerts and they are the kind of chaff that performance product companies use to fill-up their inference engine to make it look like it's actually doing something.
In fact, the vast majority of performance threshold alarms are virtually meaningless unless they have been set for your specific environment, application and situation.

And this is why consultants will always have some form of work. 🙂

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Jonathan Kehayias (12/22/2008)
And this is why consultants will always have some form of work. 🙂

True, but after 30 years of telling customers "it doesn't mean anything unless you already have a performance problem", I am getting a little tired of it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 9

You can set no of maximum user from configuration.

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Paresh Prajapati (12/22/2008)
You can set no of maximum user from configuration.

Well if they don't actually have a problem yet, they will once they follow that advice. The information that is being looked at here doesn't point to an actual problem. No configuration change should be made based on what they are seeing.

Tony++ SSCarpal Tunnel Points: 4421 More actions · Answer 11

This "Logins/second" metric came in handy for me this week. I had seen this particular metric running in the 200-400 range (using connection pooling). When my developers did a code push I saw this jump up to avg of 2200 with spikes up to 3400. Even though there's no noticable change in app performance, this clued me into inefficient changes in the data tier code.

Get to know your environment and what's "normal", and then Spotlight (or any other tool) will be useful for finding deviations.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Exactly right, Tony. Using statistics like these as diagnostic metrics or even as change indicators is fine within the context of knowing what is normal and/or expected. But arbitrary threshold alerts on them just leads to many false alarms and many repeated assurances to folks who think that they mean something.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]