0

How to set up a profiler trace to find that my CallableStatement uses connection pool?

I set a trace to capture Audit:Login , Audit:Logout, RPC:Completed. But all I see is 1-Non-Pooled (image below). Is there any configuration in Hikari/JDBC driver to get the pool to work? I would expect my second call to the service be 2-Pooled in profiler.

Hikari configuration (I used both SQLServerDataSource and SQLServerConnectionPoolDataSource for datasourceclassname, both same result):

public HikariDataSource createHikariDataSource()  {
    HikariDataSource dbDatasource = new HikariDataSource();
    dbDatasource.setDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource");
    dbDatasource.addDataSourceProperty("databaseName", "tempdb");
    dbDatasource.setPoolName("myJDBCTestConnectionPool");
    dbDatasource.setUsername("someuser");
    dbDatasource.setPassword("totallysafepassword");
    dbDatasource.setMinimumIdle(5);
    dbDatasource.setMaximumPoolSize(10);
    dbDatasource.setConnectionTimeout(300);
    dbDatasource.setIdleTimeout(1000);
    dbDatasource.setMaxLifetime(1000);
    return dbDatasource;
}

Proc execution code (redacted)

public void execMyProc() {
    HikariDataSource hkds = createHikariDataSource();
    Connection conn = hkds.getConnection();
    CallableStatement stmt3 = conn.prepareCall({? = CALL dbo.connectionpooltest @InputParam1=?});
    stmt3.registerOutParameter(1,Types.INTEGER);
    stmt3.setInt(2,1);
    ResultSet rs1 = stmt3.executeQuery();
        rs1.close();
    conn.close();
}

HikariCP and mssql-jdbc maven pom.xml entries

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>3.4.5</version>
</dependency>
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>8.4.0.jre8</version>
</dependency>

SQL Code

use tempdb;
go
create or alter proc connectionpooltest (@InputParam1 int)
as
begin
    return @InputParam1;
end;
go

Profiler screenshot:

profiler

Java Version : 1.8.281 (Language level 11 or 8)
SQL Server 2017 Developer Edition (same behavior on 2014 and 2019)

Is there anyway to reliably say that my HikariCP configuration is using a pool? How do I find it using profiler/prefmon/other tools?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
praskutti
  • 21
  • 4
  • 1
    If you are using HikariCP, then you are using a connection pool. It is unclear what you're really asking, but if I had to guess, then SQL Server simply doesn't recognize how HikariCP and the SQL Server JDBC driver works as connection pooling. Maybe [this blog post](https://www.sqlskills.com/blogs/bobb/sql-server-and-pooled-vs-non-pooled-connections/) can help you. – Mark Rotteveel Feb 07 '21 at 12:16
  • @MarkRotteveel , thanks for the link. I went through it. From what I've seen in my application, the cntr_value (in sys.dm_os_performance_counters) reduces, but in the trace I still get 1-NonPooled always :( I dont see the sp_reset_connection happening in the profiler trace. What I am trying to see in the trace is sp_reset_connection. That tells me that HikariCP is using pools right? For every call from the application I see an Audit:Login, RPC:Completed and Audit:Logout happening. – praskutti Feb 08 '21 at 00:49
  • @MarkRotteveel, what I was asking is 1. If my code/usage of Hikari is incorrectly using the pooling (I followed [this answer](https://stackoverflow.com/a/7592081/8362038) from [@baluc](https://stackoverflow.com/users/157882/balusc) , but couldnt get that sp_reset_Connection) 2. If there any tooling/logging that can tell me that sp_reset_connection is actually happening, but profiler isn't catching it? – praskutti Feb 08 '21 at 01:01

0 Answers0