none
Excessive LOG and LOGOUTs while using DAAB???? RRS feed

  • Question

  •  

    All,
    I am primarily a DBA/Database developer and have started and new engagement. I have noticed while

    profilining my clients (ASP.net) application each individual call to the database (RPC) is proceeded by a login and

    a logout. This is obvously not very efficient. The have indication that they are using the

    data access application block. Is this repeated login and log the expected behaviour???

     

     

    The other issue is that they claim that they never see more that one connection coming from 

    web servers - I have to assume that this is a configuration issue dealing with the size of the connection pool

     

    Any insite or direction would be appreciated

     

    Peace,

     

    Joe E O  

     

    Thursday, March 20, 2008 9:07 PM

Answers

  • Just a follow up.  Logout/login in SQL Profiler could be a simple reset connection.  Certain drivers will send a special token to the server to reset the connection before using it (connection is pooled and the reset clears out session state back to zero).

     

    What you need to do is look at SQL Server performance counters to get an accurate picture.  Look at:

     

    SQLServer:General Statistics -> Logins/Sec

     

    If you notice that Logins/Sec drops to zero, then this is normal reset connection activity and not true logouts/logins.

     

    For the second one this is entirely possible with efficient connection pooling that only one connection from server will appear.  This is the whole point of connection pooling to reduce true logins/logouts and re-use existing connections whenever possible.  To see the number of active connections look at:

     

     

    SQLServer:General Statistics -> User Connections

     

    I also like (as a SQL dba) to run the following queries (SQL 2005 or later):

     

    select * from sys.dm_exec_connections

     

    This will show you every connection and what client IP it comes from.

    You can also run something like this to group connections by host machine and host process:

     

    select hostname, hostprocess, count(spid) as countOfConnections from master.dbo.sysprocesses

    where spid > 50

    group by hostname, hostprocess

     

    Monday, March 24, 2008 11:46 PM

All replies

  • Hi Joseph,

    I think the following may provide you with an an answer. Because the SQL Server OLE DB provider doesn't permit more than one set of results to be pending on a connection where the results are being returned by means of a forward-only, read-only (default-resultset) cursor, the provider needs to create an additional SQL Server connection to execute a second command on the connection. The provider will only do this implicitly if the Data Source property DBPROP_MULTIPLECONNECTIONS is set to VARIANT_TRUE. 

    Please also refer to:

    http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q271128&GSSNB=1

     

     

    Regards

    Jing

    Friday, March 21, 2008 8:49 AM
  • Just a follow up.  Logout/login in SQL Profiler could be a simple reset connection.  Certain drivers will send a special token to the server to reset the connection before using it (connection is pooled and the reset clears out session state back to zero).

     

    What you need to do is look at SQL Server performance counters to get an accurate picture.  Look at:

     

    SQLServer:General Statistics -> Logins/Sec

     

    If you notice that Logins/Sec drops to zero, then this is normal reset connection activity and not true logouts/logins.

     

    For the second one this is entirely possible with efficient connection pooling that only one connection from server will appear.  This is the whole point of connection pooling to reduce true logins/logouts and re-use existing connections whenever possible.  To see the number of active connections look at:

     

     

    SQLServer:General Statistics -> User Connections

     

    I also like (as a SQL dba) to run the following queries (SQL 2005 or later):

     

    select * from sys.dm_exec_connections

     

    This will show you every connection and what client IP it comes from.

    You can also run something like this to group connections by host machine and host process:

     

    select hostname, hostprocess, count(spid) as countOfConnections from master.dbo.sysprocesses

    where spid > 50

    group by hostname, hostprocess

     

    Monday, March 24, 2008 11:46 PM