How to Monitor JDBC Communication With SQL Server

When two of our insert strategies finished in the same elapsed time but one used 4 network reads while the other used 55, I realized the benchmarks in the Fastest MS SQL Inserts series were measuring the result without seeing the mechanism. To understand why TVP was fast, I needed to see what the JDBC driver actually sent over the wire — and what SQL Server did with it.

This post documents three complementary tools I used to observe the communication between a Java application and SQL Server. None of them require Wireshark or special drivers. All of them are available on a standard developer machine.

The one thing to take from this post: you don't need a packet sniffer to understand what your app sends to SQL Server. A DMV query, an Extended Events session, or a JDBC logging flag tells you everything you need — and the data is more useful than raw TCP.

Tool 1: DMV connection statistics (fastest, zero setup)

SQL Server exposes live network statistics per connection through the sys.dm_exec_connections and sys.dm_exec_sessions dynamic management views. One query, no setup, no tracing infrastructure.

 1SELECT
 2    c.net_packet_size,
 3    c.num_reads,        -- TDS packets received from client
 4    c.num_writes,       -- TDS packets sent to client
 5    s.total_elapsed_time,
 6    s.reads AS logical_reads,
 7    s.writes AS logical_writes,
 8    s.row_count
 9FROM sys.dm_exec_connections c
10JOIN sys.dm_exec_sessions s ON s.session_id = c.session_id
11WHERE s.session_id = @@SPID;

What each column means

ColumnWhat it tells you
net_packet_sizeThe negotiated TDS packet size (default 8058 bytes). A larger packet carries more rows per round-trip.
num_readsTotal TDS packets the server received from the Java app during this session. Each addBatch() call, each Bulk Copy row, each TVP data table generates reads.
num_writesTotal TDS packets the server sent back — acknowledgments, result sets, error responses. Fewer writes = less server-to-client chatter.
logical_readsData pages read from the buffer pool. High values mean SQL Server is checking existing data (merge operations, constraint checks, index lookups).
logical_writesData pages written. Correlates with the actual rows being inserted.
row_countRows affected by the last statement. Not cumulative — snapshot per query.

How I use it in benchmarks

I query the DMV before and after each insert, then compute the delta:

 1private void captureConnectionStats(String label) {
 2    List<Map<String, Object>> stats = jdbcTemplate.queryForList(
 3        "SELECT c.net_packet_size, c.num_reads, c.num_writes, " +
 4        "s.reads AS logical_reads, s.writes AS logical_writes " +
 5        "FROM sys.dm_exec_connections c " +
 6        "JOIN sys.dm_exec_sessions s ON s.session_id = c.session_id " +
 7        "WHERE s.session_id = @@SPID"
 8    );
 9    System.out.printf("[TDS] %s: reads=%s writes=%s%n",
10        label, stats.get(0).get("num_reads"), stats.get(0).get("num_writes"));
11}
12
13@Test
14void strategy_bulkApi() {
15    captureConnectionStats("BEFORE");
16    fastInsertService.insertBulkApi(people, batchSize);
17    captureConnectionStats("AFTER");
18}

Our results (1,000 rows, batch size 100)

StrategyNetwork Reads (delta)Network Writes (delta)Interpretation
TVP441 stored-proc call = 1 round-trip for all 1,000 rows
Plain JDBC addBatch44Batched into 1 RPC call per batch group
Multi-value INSERT (200)3975 SQL statements (1,000 / 200), each parsed and planned
Bulk API3828Many TDS Bulk Copy packets (row-by-row framing)
JdbcTemplate3616~10 batch commands with acknowledgments
Hibernate persist5535Most chatty — flush/clear cycles, entity-state sync

The surprise: TVP and Plain JDBC produce just 4 network reads for 1,000 rows. Hibernate generates 14x more. The bulk-copy protocol is chattier than TVP because each row occupies its own TDS frame, even though the elapsed time is faster (the TDS frames are cheaper to parse than SQL commands).

When to use DMV stats

  • Quick sanity check during development — "is my batch really batching?"
  • Comparing strategies — the delta reveals whether the driver is sending one big blob or many small ones.
  • Production monitoring — query the DMV periodically to watch for chatty connections.

Limitations

  • Cumulative per session, not per query. You need the before/after delta.
  • num_reads/num_writes count TDS packets, not SQL statements. One TDS packet can carry multiple SQL statements.
  • Doesn't tell you what was inside each packet — for that, use Extended Events or JDBC logging.

Tool 2: SQL Server Extended Events (server-side capture)

Extended Events (XE) is SQL Server's built-in tracing infrastructure. It captures what SQL Server receives — the parsed SQL commands, the RPC calls, the timing and row counts — at the server side.

Setting up the capture

 1CREATE EVENT SESSION TDS_Benchmark ON SERVER
 2ADD EVENT sqlserver.sql_batch_completed (
 3    ACTION (sqlserver.session_id, sqlserver.sql_text, sqlserver.database_name,
 4            package0.event_sequence)
 5    WHERE sqlserver.database_name = N'testdb'
 6),
 7ADD EVENT sqlserver.rpc_completed (
 8    ACTION (sqlserver.session_id, sqlserver.sql_text, package0.event_sequence)
 9    WHERE sqlserver.database_name = N'testdb'
10)
11ADD TARGET package0.event_file (SET filename = N'C:\temp\tds_benchmark.xel', max_file_size = 64)
12WITH (MAX_MEMORY = 4096 KB, MAX_DISPATCH_LATENCY = 1 SECONDS);
13
14ALTER EVENT SESSION TDS_Benchmark ON SERVER STATE = START;

What to capture

EventWhat it tells you
sql_batch_completedA SQL text batch (one or more statements) finished. Captures the SQL text, duration, CPU, reads, writes, row count.
rpc_completedA stored procedure or parameterized query finished via an RPC call. TVP and addBatch() use this path.
sql_batch_startingWhen the batch started — useful for timing analysis.

How to read the captured data

 1-- Stop the session when done
 2ALTER EVENT SESSION TDS_Benchmark ON SERVER STATE = STOP;
 3
 4-- Query the file target (much faster than ring_buffer for large captures)
 5SELECT
 6    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_type,
 7    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration_us,
 8    event_data.value('(event/data[@name="row_count"]/value)[1]', 'bigint') AS row_count,
 9    LEFT(event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)'), 200) AS sql_text
10FROM sys.fn_xe_file_target_read_file('C:\temp\tds_benchmark*.xel', NULL, NULL, NULL)
11CROSS APPLY (SELECT CAST(event_data AS xml)) AS t(event_data);

What the events reveal

For each insert strategy, the XE trace shows:

  • Hibernate persist: many sql_batch_completed events — one per batch flush cycle, each containing an INSERT statement with parameters.
  • TVP: one rpc_completed event per stored-procedure call. The SQL text shows EXEC usp_InsertPersons @persons = ... — the entire data table sent as a single parameter.
  • Bulk API: no sql_batch_completed events at all — Bulk Copy uses the TDS Bulk Load protocol, which is invisible to sql_batch_completed. You need the bulk_insert_completed event instead.

Limitations

  • Ring buffer target is slow for large captures (999+ events = XML parsing timeout). Use a file target (event_file) for anything more than a quick check.
  • Bulk Copy operations don't fire sql_batch_completed — they use a different TDS protocol path. Add sqlserver.bulk_insert_completed to see them.
  • XE adds ~1-3% overhead to the captured workload. Acceptable for analysis; don't leave it on in production benchmarks.

Tool 3: JDBC driver TDS logging (client-side view)

The Microsoft JDBC Driver for SQL Server has built-in TDS-level logging. When enabled, it logs every TDS command the driver sends — the exact framing, parameters, and protocol messages.

Enabling it

Add these parameters to your JDBC URL:

jdbc:sqlserver://localhost:1433;databaseName=testdb;
  encrypt=true;trustServerCertificate=true;
  logger=java.util.logging.Logger;
  loggerLevel=FINE

Or configure programmatically via java.util.logging:

1Logger.getLogger("com.microsoft.sqlserver.jdbc").setLevel(Level.FINE);

What it shows

With logging at FINE, the driver logs:

  • Every TDS packet sent and received (direction, size, type)
  • SQL text sent to the server
  • Parameter bindings for parameterized queries
  • Bulk Copy frame headers and row counts
  • TVP data table metadata and row packaging

At FINEST, you get hex dumps of the raw TDS bytes — useful for protocol-level debugging but very verbose.

When to use JDBC logging vs. Extended Events

QuestionUse this tool
"What SQL does my app send?"Extended Events (sql_batch_completed)
"How does the driver package the data?"JDBC logging (TDS frame structure)
"How many network round-trips per batch?"DMV num_reads / num_writes
"What does SQL Server do with what it receives?"Extended Events (rpc_completed + execution stats)

The three tools are complementary. DMV stats give you the count; XE gives you the server's view; JDBC logging gives you the client's view. Use all three and compare.

Putting it together: a monitoring workflow

For any new insert strategy I benchmark, I run this three-step workflow:

  1. DMV before/after — how many TDS packets did the insert produce? (5 seconds)
  2. XE file capture — what SQL commands or RPC calls did SQL Server process? (30 seconds)
  3. JDBC FINE logging — how did the driver package the data? (enable, run one insert, read the log)

The first step is enough for most comparisons. The second adds "what" to the "how many." The third is only needed when the protocol-level framing matters (Bulk Copy vs. TVP vs. regular INSERT).