logins/sec high and logouts/sec near zero and yet total sessions are around 70

So i had perfmon running and opened logins/sec and logouts/sec under
SQLServer:General statistics and noticed logins/sec were around always
moving around from 70-240, but logouts/sec is near zero during the time.
So per that metric, i felt we would soon be around 30000 connections and
have sql server rejecting any more connections.. so doing a count(*) from
sys.dm_exec_sessions only shows 71 which also incude session_id < = 50 for
system sessions.
So why do I see that ? I was under the impression that if we dont use
connection pooling, then logouts/sec would also be as high as logins/sec
especially since total connections are only around 20 for user sessions.
Using SQL 2005
Thanks
That's your connection pooling at work. If you or the program sets the
pooling parameter on the connection string to false, you would see logins/sec
match logouts/sec.
There isn't much to worry about the performance impact of a large number of
logins/sec with connection pooling. See
http://sqlblog.com/blogs/linchi_shea/archive/2007/04/16/performance-impact-of-frequent-logins-actually-performance-impact-of-frequently-calling-sqlconnection-open-with-connection-reset.aspx for some empirical data.
Linchi
"Hassan" wrote:

> So i had perfmon running and opened logins/sec and logouts/sec under
> SQLServer:General statistics and noticed logins/sec were around always
> moving around from 70-240, but logouts/sec is near zero during the time.
> So per that metric, i felt we would soon be around 30000 connections and
> have sql server rejecting any more connections.. so doing a count(*) from
> sys.dm_exec_sessions only shows 71 which also incude session_id < = 50 for
> system sessions.
> So why do I see that ? I was under the impression that if we dont use
> connection pooling, then logouts/sec would also be as high as logins/sec
> especially since total connections are only around 20 for user sessions.
> Using SQL 2005
> Thanks
>
|||Ok cool.. Thanks Linchi..
Btw, do you have an answer for the elapsed_time posting of mine ? ;)
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:5F983468-504D-49B2-8C32-CC789783DA9E@.microsoft.com...[vbcol=seagreen]
> That's your connection pooling at work. If you or the program sets the
> pooling parameter on the connection string to false, you would see
> logins/sec
> match logouts/sec.
> There isn't much to worry about the performance impact of a large number
> of
> logins/sec with connection pooling. See
> http://sqlblog.com/blogs/linchi_shea/archive/2007/04/16/performance-impact-of-frequent-logins-actually-performance-impact-of-frequently-calling-sqlconnection-open-with-connection-reset.aspx
> for some empirical data.
> Linchi
> "Hassan" wrote:

logins/sec high and logouts/sec near zero and yet total sessions are around 70

So i had perfmon running and opened logins/sec and logouts/sec under
SQLServer:General statistics and noticed logins/sec were around always
moving around from 70-240, but logouts/sec is near zero during the time.
So per that metric, i felt we would soon be around 30000 connections and
have sql server rejecting any more connections.. so doing a count(*) from
sys.dm_exec_sessions only shows 71 which also incude session_id < = 50 for
system sessions.
So why do I see that ? I was under the impression that if we dont use
connection pooling, then logouts/sec would also be as high as logins/sec
especially since total connections are only around 20 for user sessions.
Using SQL 2005
ThanksThat's your connection pooling at work. If you or the program sets the
pooling parameter on the connection string to false, you would see logins/sec
match logouts/sec.
There isn't much to worry about the performance impact of a large number of
logins/sec with connection pooling. See
http://sqlblog.com/blogs/linchi_shea/archive/2007/04/16/performance-impact-of-frequent-logins-actually-performance-impact-of-frequently-calling-sqlconnection-open-with-connection-reset.aspx for some empirical data.
Linchi
"Hassan" wrote:
> So i had perfmon running and opened logins/sec and logouts/sec under
> SQLServer:General statistics and noticed logins/sec were around always
> moving around from 70-240, but logouts/sec is near zero during the time.
> So per that metric, i felt we would soon be around 30000 connections and
> have sql server rejecting any more connections.. so doing a count(*) from
> sys.dm_exec_sessions only shows 71 which also incude session_id < = 50 for
> system sessions.
> So why do I see that ? I was under the impression that if we dont use
> connection pooling, then logouts/sec would also be as high as logins/sec
> especially since total connections are only around 20 for user sessions.
> Using SQL 2005
> Thanks
>|||Ok cool.. Thanks Linchi..
Btw, do you have an answer for the elapsed_time posting of mine ? ;)
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:5F983468-504D-49B2-8C32-CC789783DA9E@.microsoft.com...
> That's your connection pooling at work. If you or the program sets the
> pooling parameter on the connection string to false, you would see
> logins/sec
> match logouts/sec.
> There isn't much to worry about the performance impact of a large number
> of
> logins/sec with connection pooling. See
> http://sqlblog.com/blogs/linchi_shea/archive/2007/04/16/performance-impact-of-frequent-logins-actually-performance-impact-of-frequently-calling-sqlconnection-open-with-connection-reset.aspx
> for some empirical data.
> Linchi
> "Hassan" wrote:
>> So i had perfmon running and opened logins/sec and logouts/sec under
>> SQLServer:General statistics and noticed logins/sec were around always
>> moving around from 70-240, but logouts/sec is near zero during the time.
>> So per that metric, i felt we would soon be around 30000 connections and
>> have sql server rejecting any more connections.. so doing a count(*) from
>> sys.dm_exec_sessions only shows 71 which also incude session_id < = 50
>> for
>> system sessions.
>> So why do I see that ? I was under the impression that if we dont use
>> connection pooling, then logouts/sec would also be as high as logins/sec
>> especially since total connections are only around 20 for user sessions.
>> Using SQL 2005
>> Thanks
>>

logins/sec

I'm monitoring 2005 while running our application by looking at

sys.dm_os_performance_counters, general statistics, logins/sec.

I began testing a .NET 2 application, and logins/sec went from 63 to 11,433 in 30 minutes. Even though I have closed the application, it is still showing 11,449 - the only thing that's happened in the last hour is that I've run some queries from QA. is this login number cumulative, or is it an actual reflection of logins/sec? How on earth could that many logins be happening when no one else can even get to that server?

Hi,

This counter is indeed cumulative in the dmv.

I would check out the .NET application if it is creating 11000+ logins in 30 minutes.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Try to log the logins on the server using the logging functionality on the server, logging the failed as well as the successfull logins.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

in looking at the event viewer, there are hardly any logon/logout events.

Logging all logins.

?

|||

Here's what I'm seeing over and over in my trace. Our stored procedure should be writing a record each time, but it doesn't come accross in the trace as anything but a read. I have verified that the data is actually getting written to the table.

All spid 62.

All application name = .Net SqlClient Data Provider

EventClass textdata reads writes

Audit Logout NULL 68 4
RPC:Completed exec sp_reset_connection 0 0
Audit Login -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed NULL NULL

RPC:Completed exec <<<our stored procedure>>> 2 0
Audit Logout NULL 70 4

RPC:Completed exec sp_reset_connection 0 0
Audit Login -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed NULL NULL

RPC:Completed exec <<our stored procedure>> 2 0

Audit Logout NULL 72 4

|||Did you try to implement something like an audit mechanism, to track the written information or execution information in a table ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||not sure what you mean...some kind of trigger?|||Yep.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

login statistics

I am wondering if there is an sp in mssql server that is
equivalent to the sybase sp_reportstats.
This sp prints out the current accounting totals for all
logins, as well as each login's individual statistics and
percentage of the overall statistics.
eg
loginname, cpu, percent of cpu, I/O percent I/OHi,
Have a look into the procedure SP_WHO2 and the master..sysprocesses system
table.
Thanks
Hari
MCDBA
"Bola" <anonymous@.discussions.microsoft.com> wrote in message
news:18e0701c41bc5$9da48a50$a401280a@.phx
.gbl...
> I am wondering if there is an sp in mssql server that is
> equivalent to the sybase sp_reportstats.
> This sp prints out the current accounting totals for all
> logins, as well as each login's individual statistics and
> percentage of the overall statistics.
> eg
> loginname, cpu, percent of cpu, I/O percent I/O