Sql-server – Hundreds of TCP connections opened by sqlservr.exe

connection-poolingsql serversql-server-2008-r2

We have Tomcat on one machine and SQL Server on another.

Our SQL Server machine is Windows 2008 R2 64bit machine with 32GB usable RAM
4 processors, 16 cpus.
We use database pooling to keep db connection reusable.

I am looking at the Resource Monitor in Task Manager and I noticed hundrerds of TCP connections from application server to SQL Server machine.

There are 1014 total connections.

1009 of them are 'AWAITING COMMAND', status = 'sleeping'.
Other 5 are 'SELECT' and 'UPDATE', status = 'runnable'.

enter image description here
Does anyone know why is this happening?

My application-context.xml:

<bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="properties">
        <props>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.c3p0.min_size">4</prop>
            <prop key="hibernate.c3p0.max_size">10</prop>
            <prop key="hibernate.c3p0.timeout">600</prop>
            <prop key="hibernate.c3p0.max_statements">25</prop>
            <prop key="hibernate.c3p0.testConnectionOnCheckout">false</prop>
            <prop key="hibernate.cache.use_second_level_cache">false</prop>
        </props>
        </property>
    </bean>

And my c3p0.properties:

c3p0.maxIdleTime=900
c3p0.maxStatements=150
c3p0.maxIdleTimeExcessConnections=300
numHelperThreads=6
unreturnedConnectionTimeout=3600

Best Answer

Connection pooling is designed to keep connections open. If your connection pool is set with a maximum pool size of 100 connections, you may well see 100 connections open on the server per intentional connection opened by the client.

I tested this with a simple VB.Net client targeted against my local SQL Server. The .Net code is:

Module Module1

    Sub Main()
        Dim cb As SqlClient.SqlConnectionStringBuilder = New SqlClient.SqlConnectionStringBuilder
        cb.ApplicationName = "MyTestApp"
        cb.ConnectTimeout = 30
        cb.DataSource = "<servername>"
        cb.InitialCatalog = "tempdb"
        cb.IntegratedSecurity = True
        cb.MultipleActiveResultSets = False
        cb.Pooling = True
        cb.MinPoolSize = 10
        cb.MaxPoolSize = 100
        cb.WorkstationID = My.Computer.Name
        Using cn As New SqlClient.SqlConnection(cb.ConnectionString)
            cn.Open()
            Using c As New SqlClient.SqlCommand("SELECT DB_NAME();", cn)
                Dim r As SqlClient.SqlDataReader = c.ExecuteReader()
                While r.Read
                    Console.WriteLine(r.GetValue(0))
                End While
                r.Close()
            End Using
            Console.ReadKey() ' wait for the user to press a key before we exit.
        End Using

    End Sub

End Module

The code creates what looks like a single connection to my SQL Server, and executes a single very simple query returning the name of the database. It then "closes" the data reader, returning the connection to the pool.

Running this, then looking at the SQL Server connections with this query:

SELECT des.session_id
    , des.login_time
    , des.program_name
    , des.status
FROM sys.dm_exec_sessions des
WHERE des.program_name = 'MyTestApp';

shows the following results:

╔════════════╦═════════════════════════╦══════════════╦══════════╗
║ session_id ║       login_time        ║ program_name ║  status  ║
╠════════════╬═════════════════════════╬══════════════╬══════════╣
║         65 ║ 2018-04-11 15:18:00.517 ║ MyTestApp    ║ sleeping ║
║         66 ║ 2018-04-11 15:18:00.520 ║ MyTestApp    ║ sleeping ║
║         67 ║ 2018-04-11 15:18:00.523 ║ MyTestApp    ║ sleeping ║
║         68 ║ 2018-04-11 15:18:00.523 ║ MyTestApp    ║ sleeping ║
║         69 ║ 2018-04-11 15:18:00.527 ║ MyTestApp    ║ sleeping ║
║         70 ║ 2018-04-11 15:18:00.527 ║ MyTestApp    ║ sleeping ║
║         71 ║ 2018-04-11 15:18:00.530 ║ MyTestApp    ║ sleeping ║
║         72 ║ 2018-04-11 15:18:00.533 ║ MyTestApp    ║ sleeping ║
║         73 ║ 2018-04-11 15:18:00.533 ║ MyTestApp    ║ sleeping ║
║         74 ║ 2018-04-11 15:18:00.537 ║ MyTestApp    ║ sleeping ║
╚════════════╩═════════════════════════╩══════════════╩══════════╝

Connection pooling is creating 10 connections (the minimum I specified in the code above), and keeps them open as long as the app is running.

Related Question