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.

  • 1
    It pretty much depends. If you have, say, restarted a bunch of services and applications that connect to Sql Server databases, I wouldn't be worried. If this occurs all the time, it is time to investigate more closely. – vonPryz Oct 26 '16 at 9:44
  • @vonPryz,can you please tell me if I take some downtime and re-start the SQL Server services this issue will be gone.... can you please elaborate little bit on this issue as I am bit of new in dealing with Perfmon Counter Alerts. – SQLBoy Oct 26 '16 at 9:48
  • Please explain why you think that restarting Sql Server would improve the situation? It doesn't, so I guess there's a misunderstanding. – vonPryz Oct 26 '16 at 9:59
  • Oh my mistake, may be I am with wrong assumption and thinking restart my drop of and refresh the connections... – SQLBoy Oct 26 '16 at 10:01
1

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.

  • Can you tell me if this is a serious Alert and how can I get rid of this Alert, any suggestions or advice please let me know. – SQLBoy Oct 26 '16 at 9:40
  • 1
    It could be serious if you are not expecting this activity. You need to look into who or what is making these connections and why. It could just be that your app is busier now and so it makes more connections or it could be that someone or something else has started connecting for some reason... – James Anderson Oct 26 '16 at 9:45
  • Thank you so much @James Anderson,i think I bit understood about this issue and will continue monitoring the sessions and connections to our Database Server. – SQLBoy Oct 26 '16 at 9:50
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/

  • Thank you so much @TheGameiswar for the response and I think I got some clue from your response and will try to fetch more information regarding the issue. – SQLBoy Oct 26 '16 at 9:51
  • 1
    I think Logins/sec counts the initial login of a connection that will be pooled. If connections are not being pooled because of different connection strings or credentials then you might see the high Logins/sec. For this reason I would say connection pooling reusability could be the issue. – James Anderson Oct 26 '16 at 10:02
  • Thanks a lot @JamesAnderson for the helpful response,will check more on it. – SQLBoy Oct 26 '16 at 10:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.