SQL Server – How to Deal with Performance Counters

connection-poolingsql serversql server 2014

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.

Best Answer

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.

Related Question