Number of connections differ between performance monitor and SQL Server Profiler

Question

WHAT I'M TRYING TO FIND OUT

How many logins/s and logouts/s I'm having on my SQL Server 2016 Standard.

WHAT I DID

SQL SERVER PROFILER

In SQL Server Profiler, I made a trace selecting only 'Audit Login' and 'Audit Logout'. The trace runned for about 10 seconds, and I had about 10000 lines of trace.

I tested this a number of times, and also with only 'Audit Login' or 'Audit Logout', and it's about 5000 each for 10 secconds, meaning about 500 logins/s.

PERFORMANCE MONITOR

In Performance Monitor, I selected the counters 'Logins/sec', 'Logouts/sec' and 'User Connections' in SQLServer:General Statistics Section.

I'm getting average of 3,5 logins and logouts per second.

THE PROBLEM

The number of logins/logout in SQL Server observed through Performance Monitor and SQL Server Profiler differ by a large margin.

What I'm reading wrong here?

Tks in advance.

score 1 · Answer 1 · answered Jun 10 '21 at 16:06

From my experience, when you setup SQL Server Audit or Extended Events sessions - both of those kind of similar to Profiler / SQL Trace in terms of monitoring for logins/logouts, these tend to generate a lot of rows (records) of login event, when you logged in just once in SSMS or application

In other words, just one login generates several events in the logs
Not sure how to explain above, maybe someone here knows much more on this

But in short, Performance Monitor shows numbers of "true" logins, while Profiler produces much more events per one "real" login

It seems more fit to the reality that the numbers in PerfMon are true. While I can't explain from where the numbers in Profiler/Tracer comes from, it has a lot of similarities with what you provided. — chesterman, Jun 10 '21 at 16:34

Dan Guzman · Accepted Answer · 2021-06-10T17:00:07.020

The internal sp_reset_connection stored procedure performs many actions that generates events when the first query using a resused pooled connection is executed. Among these is the Audit Logout/Login events you see in Profiler.

The performance monitor counters include only login/logouts that occur with new and closed physical connections, not reused pooled connections. Consequently, the number of login events shown in Profiler will be dramatically higher for applications employ connection pooling.