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 (Sessions)

Hi !
Is it possible to backup (and restore) logins (no database users)? I've this doubt because if I backup a db (Db1) from SQLserver1 and then I restore it in other server (SQLserver2) without all logins than SQLserver1, results in a problem with user access
to database.
Any comments? thanks in advance.
Have a look here:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Han" <Han@.discussions.microsoft.com> wrote in message
news:9245067A-245E-41A4-97F8-4FB7F51A03CD@.microsoft.com...
> Hi !
> Is it possible to backup (and restore) logins (no database users)? I've
this doubt because if I backup a db (Db1) from SQLserver1 and then I restore
it in other server (SQLserver2) without all logins than SQLserver1, results
in a problem with user access to database.
> Any comments? thanks in advance.
>

Logins (Sessions)

Hi !
Is it possible to backup (and restore) logins (no database users)? I've this doubt because if I backup a db (Db1) from SQLserver1 and then I restore it in other server (SQLserver2) without all logins than SQLserver1, results in a problem with user access to database.
Any comments? thanks in advance.Have a look here:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Han" <Han@.discussions.microsoft.com> wrote in message
news:9245067A-245E-41A4-97F8-4FB7F51A03CD@.microsoft.com...
> Hi !
> Is it possible to backup (and restore) logins (no database users)? I've
this doubt because if I backup a db (Db1) from SQLserver1 and then I restore
it in other server (SQLserver2) without all logins than SQLserver1, results
in a problem with user access to database.
> Any comments? thanks in advance.
>

Logins (Sessions)

Hi !
Is it possible to backup (and restore) logins (no database users)? I've this
doubt because if I backup a db (Db1) from SQLserver1 and then I restore it
in other server (SQLserver2) without all logins than SQLserver1, results in
a problem with user access
to database.
Any comments? thanks in advance.Have a look here:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Han" <Han@.discussions.microsoft.com> wrote in message
news:9245067A-245E-41A4-97F8-4FB7F51A03CD@.microsoft.com...
> Hi !
> Is it possible to backup (and restore) logins (no database users)? I've
this doubt because if I backup a db (Db1) from SQLserver1 and then I restore
it in other server (SQLserver2) without all logins than SQLserver1, results
in a problem with user access to database.
> Any comments? thanks in advance.
>