Logins / Sec Perf Counter Odd Results

Question

Logins / Sec Perf Counter Odd Results

David Benoit

SSC-Dedicated

Points: 34562
More actions
October 27, 2009 at 9:36 am

#212624

I am doing some monitoring with performance monitor, specifically with the SQLServer:General Statistics Logins/sec counter against two servers which should be seeing very similar load. The interesting thing here is that the one server is showing about 9 times greater Logins/sec and Logouts/sec than the other.
Here is where it gets odd. I decided I was going to run a trace against both servers and collect the connection sources (hostname) to see if there was one that was generating more connections and then try to find out if connection pooling (or some other connection option) was mis-configured. However, the trace data showed that the # of logins / logouts were pretty much identical between the two servers. So, it appears that the performance monitor counter is seeing something else come through as a login that the trace is not.
Has anyone else seen this? Very curious. Thanks in advance for any information you have.
David
@SQLTentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Viewing 5 posts - 1 through 4 (of 4 total)

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

ServerIntellect_PC SSC Veteran Points: 209 More actions · Answer 1

David,

I have seen this before and so far it looks like the perf counter is picking up the logins and when a connection is reused. What I have read is that this was a performance counter change between versions.

Thanks

EDIT:

Found this: http://msdn.microsoft.com/en-us/library/ms187961.aspx

The sp_reset_connection stored procedure is used by SQL Server to support remote stored procedure calls in a transaction. This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool.

Paul Cortes
Technical Support Representative
http://www.ServerIntellect.com
Managed Servers, 24x7 U.S. Support, Web Hosting Solutions.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 2

ServerIntellect_PC (10/28/2009)
David,
I have seen this before and so far it looks like the perf counter is picking up the logins and when a connection is reused. What I have read is that this was a performance counter change between versions.
Thanks

Thanks Paul - Pretty odd still as the two instances I am watching are both identical versions.

Your explanation may be correct since I was not tracing existing connections. Should have done that too to see what showed up. If it shows up again I will be certain to change my trace a bit and see if that is indeed the situation.

Thanks again.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

ServerIntellect_PC SSC Veteran Points: 209 More actions · Answer 3

Hi David,

I edited my reply but I was not sure if it would notify you so here is something interesting that I found:

http://msdn.microsoft.com/en-us/library/ms187961.aspx