1

Recently we got this alert from monitoring team.

SQLServer:General Statistics Logins/sec has exceeded the threshold of 2

I tried to know a bit about it and if the value is less than

2 per second indicates that the application is not correctly using connection pooling.

What does Application is not correctly using the connection pooling -- What does it mean ?

And few DMVs might help to find out the Logins/sec like the session_id and application

sys.dm_exec_sessions 
sys.dm_exec_connections 
sys.dm_exec_requests

But I am not able to connect the link between the Logins/sec and session ids. Can anyone help me to understand how to deal with performace counters and does number of connections = number of session_ids (SPIDs) ,is this true ...Please help.

Thanks.

James Anderson
  • 5,794
  • 2
  • 27
  • 43
Learning_Learning
  • 1,620
  • 25
  • 49

2 Answers2

2

From the documentation Connection Pooling is:

Connecting to a data source can be time consuming. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections

Basically the provider keeps connections open, even if the code that opened them closes them. The next time the provider needs a connection the already open one is used.

These open connections are grouped into pools. A new pool is created for each combination of connection string and credential used to access SQL Server.

Having two logins per second could mean that connection pooling isn't working correctly or it could mean that there are multiple connections using a different combination of connection strings and credentials.

James Anderson
  • 5,794
  • 2
  • 27
  • 43
1

2 per second indicates that the application is not correctly using connection pooling.

What does Application is not correctly using the connection pooling -- What does it mean ?

From the Documentation for this counter..

Logins/sec Total number of logins started per second. This does not include pooled connections.

So you need not worry about connection pooling reusability..

I am not able to connect the link between the Logins/sec and session ids

This has been answered by Remus Rusanu here :What is the difference between a connection and a session? ,normally connection and session have one to one mapping..

The connection is the physical communication channel between SQL Server and the application: the TCP socket, the named pipe, the shared memory region. The session in SQL Server corresponds to the Wikipedia definition of a session: a semi-permanent container of state for an information exchange. In other words the sessions stores settings like cache of your login information, current transaction isolation level, session level SET values etc etc.

Normally there is one session on each connection, but there could be multiple session on a single connection (Multiple Active Result Sets, MARS) and there are sessions that have no connection (SSB activated procedures, system sessions). There are also connections w/o sessions, namely connections used for non-TDS purposes, like database mirroring sys.dm_db_mirroring_connections or Service Broker connections sys.dm_broker_connections.

To Practice and understand more on this,this link helped me,you can take a look..

https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/10/08/connection-pooling-for-the-sql-server-dba/

Community
  • 1
TheGameiswar
  • 2,999
  • 4
  • 30
  • 50