High memory allocation of TDSPacket objects, leading to OOME (table with binary data column)

[jubax]

Driver version

12.10.0.jre11

SQL Server version

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )

Client Operating System

Windows Server 2019 Standard

JAVA/JVM version

OpenJDK 64-Bit Server VM Temurin-21.0.5+11 (build 21.0.5+11-LTS, mixed mode, sharing)

Table schema

CREATE TABLE #MY_STAGING_TABLE
(
	HASH binary(32) NOT NULL,
	ID bigint NOT NULL
);

Problem description

We are inserting rows into a staging table. We call 64 times PreparedStatement.addBatch() (where each batch has 1024 rows) and then call PreparedStatement.executeBatch(). This code structure works fine for basically any data type.

We call PreparedStatement.setLong() and PreparedStatement.setBytes(), where the bytes is always a byte[32].

The problem is that executeBatch() allocates way too much memory.

Note that the allocated memory before the executeBatch() call is just a few 100 MBs.

Expected behavior

A reasonable amount of memory allocation by the JDBC driver

Actual behavior

Very high allocation. The screenshot below is from the Eclipse Heap Analyzer tool (this is from a run where we limited the main memory to 12 GB):

The heap analyzer also shows a histogram:

Error message/stack trace

OutOfMemoryError

Any other details that can be helpful

n/a

JDBC trace logs

n/a

I just wanted to create this bug report to get some initial feedback before spending the time for a complete Java example; maybe there already is a known problem with binary data?

Thanks

[machavan]

Thanks @jubax for reporting it. We are not aware of an existing problem with this. Can you please share your sample application that helped you observe the behavior?

[jubax]

This is harder to reproduce with a small program than anticipated (but I keep working on it). But what I found out so far:

try (PreparedStatement ps1 = connection.prepareStatement("SELECT id, value .. (complicated SQL with millions of results)")) {
    try (ResultSet rs = ps1.executeQuery()) {
        while (rs.next()) {
            long id = rs.getLong(1);
            String lsid = rs.getString(2);
            byte[] data = calcSha3_256(lsid);
        }
    }
}

works as expected. No huge memory increase etc.

But if I execute an insert operation with a single batch then the executeBatch() call needs a long time to finish and the memory demand keeps growing until 20 GB or so.

try (PreparedStatement ps1 = connection.prepareStatement("SELECT id, value .. (complicated SQL with millions of results)")) {
    try (ResultSet rs = ps1.executeQuery()) {
        while (rs.next()) {
            long id = rs.getLong(1);
            String lsid = rs.getString(2);
            byte[] data = calcSha3_256(lsid);

            // NOTE: Just a single batch, for illustration purposes
            ps2.setLong(1, id);
            ps2.setBytes(2, data);
            ps2.addBatch();

            ps2.executeBatch();
        }
    }
}

I have the impression that executeBatch() somehow causes fetching of the whole result set before it can actually proceed.

[jubax]

I finally could reproduce the problem:

try (Connection connection = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=MYDB;trustServerCertificate=true", <user>, <password>)) {
    connection.setAutoCommit(false);
    try {
        boolean prepareLargeTable = false;
        if (prepareLargeTable) {
            boolean dropTable = false;
            if (dropTable) {
                try (PreparedStatement statement = connection.prepareStatement("IF OBJECT_ID('large_table', 'U') IS NOT NULL DROP TABLE large_table;")) {
                    statement.execute();
                }
            }
            try (PreparedStatement statement = connection.prepareStatement("IF OBJECT_ID('large_table', 'U') IS NULL CREATE TABLE large_table (att1 BIGINT NOT NULL, att2 NVARCHAR(100), att3 NVARCHAR(100) NOT NULL);")) {
                statement.execute();
            }
            System.out.println("insert into large table ...");
            try (PreparedStatement statement = connection.prepareStatement("INSERT INTO large_table VALUES(?, ?, ?);")) {
                int batchCount = 0;
                for (int i = 0; i < 100_000_000; i++) {
                    UUID uuid = UUID.randomUUID();

                    statement.setLong(1, i);
                    statement.setString(2, uuid.toString().toLowerCase(Locale.ROOT));
                    statement.setString(3, uuid.toString().toUpperCase(Locale.ROOT));
                    statement.addBatch();

                    if (i > 0 && i % 100_000 == 0) {
                        statement.executeBatch();
                        System.out.println("executed batch #" + ++batchCount + ", total: " + i);
                    }
                }
                statement.executeBatch();
            }
            System.out.println("insert into large table ... done.");
            connection.commit();
        }

        try (PreparedStatement statement = connection.prepareStatement("IF OBJECT_ID('staging', 'U') IS NOT NULL DROP TABLE staging;")) {
            statement.execute();
        }
        try (PreparedStatement statement = connection.prepareStatement("CREATE TABLE staging (att1 BIGINT NOT NULL, att2 NVARCHAR(100));")) {
            statement.execute();
        }

        System.out.println("insert into staging table ...");
        try (
                PreparedStatement ps1 = connection.prepareStatement("""
                                select att1 as x1, att2 as x2 from large_table
                                union
                                select att1 as x1, att3 as x2 from large_table
                                union
                                select att1 as x1, att2 + att3 as x2 from large_table
                                union
                                select att1 as x1, att3 + att2 as x2 from large_table
                        """);
                PreparedStatement ps2 = connection.prepareStatement("INSERT INTO staging (att1, att2) VALUES(?, ?);");) {

            try (ResultSet rs = ps1.executeQuery()) {
                while (rs.next()) {
                    long id = rs.getLong(1);
                    String uuid = rs.getString(2);

                    // Add a single batch
                    ps2.setLong(1, id);
                    ps2.setString(2, uuid);
                    ps2.addBatch();

                    long t0 = System.currentTimeMillis();
                    System.out.println("execute batch ...");
                    // Executing this batch takes a long time while the heap memory is increasing to several GB.
                    // => It seems the outer query is executed in the background
                    ps2.executeBatch();
                    System.out.println("execute batch ... done. Time: " + (System.currentTimeMillis() - t0) + " ms");
                    break;
                }
            }
        }
    } finally {
        try (PreparedStatement statement = connection.prepareStatement("IF OBJECT_ID('staging', 'U') IS NOT NULL DROP TABLE staging;")) {
            statement.execute();
        }
    }
}

So,

• Contrary to the original description this has nothing to do with binary data.
• The size of the batch is irrelevant. The problem is reproducible with a single batch

@machavan, as my original description was wrong I wonder if I a) should keep the bug as it is, b) adapt the description or c) create a new bug and close this one?

A workaround would be highly appreciated. I already tried executing the batch in a separate thread, but no avail.

Thanks and sorry for the initial bug description.

[machavan]

Hi @jubax thank you for reporting this with a repro!

While we look into it and analyze further can you try out the Bulk Copy API (https://learn.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver16) ?

You can use your existing repro code but specify the addition connection string option to enable bulk copy API and also specify various options to fine-tune it like batch size etc.

[jubax]

Hi @jubax thank you for reporting this with a repro!

While we look into it and analyze further can you try out the Bulk Copy API (https://learn.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver16) ?

You can use your existing repro code but specify the addition connection string option to enable bulk copy API and also specify various options to fine-tune it like batch size etc.

Simply adding this option to the JDBC connection string does not change the behavior. I tried it out with the default fetch size and with the fetch size of 1000. No change. It seems as if the execution of the batch triggers the retrieval of the full result set.

For reference: I also tried out SQLServerBulkCopy, which also runs into the same problem.

[machavan]

Hi @jubax

Can you please share the stack trace and preferably the (FINEST) level trace file. You can enable the traces with:
https://docs.microsoft.com/sql/connect/jdbc/tracing-driver-operation

I am assuming that you saw out of memory error in the second part of your program that selects data from large_table with a 4 times union on it.

I want to highlight that there are certain performance optimization opportunities which we are already exploring when it comes to reading large volumes of data from server. This includes optimizing the usage of byte[] which olds the TDSPacket data as it is streamed from server.

[jubax]

Hi, I'm not sure I can provide a stack trace and the package information the next two weeks, so I wanted to give some answers in the meantime:

Hi @jubax

Can you please share the stack trace and preferably the (FINEST) level trace file. You can enable the traces with: https://docs.microsoft.com/sql/connect/jdbc/tracing-driver-operation

The OOME happend in ps2.executeBatch();, as far as I remember while allocating a TDSPacket or a byte array. I already run this test with FINEST and the packets simply show that those are the packets coming from the outer query.

See the two screenshots of the heap analyzer tool in the original description. That is pretty much what's going on there.

I am assuming that you saw out of memory error in the second part of your program that selects data from large_table with a 4 times union on it.

Yes, in ps2.executeBatch();. Note that it is only needed to fill the large_table once and keep it.

I want to highlight that there are certain performance optimization opportunities which we are already exploring when it comes to reading large volumes of data from server. This includes optimizing the usage of byte[] which olds the TDSPacket data as it is streamed from server.

The problem does not seem to be inefficient packet/data handling. The problem seems to be that ps2.executeBatch(); somehow triggers the driver to retrieve all the results from the outer query before proceeding the batch.

Thank you

[jubax]

The output logfile is already >10 GB a minute after executeBatch() has been called. Most of the output is packet information like

com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream ensureSSLPayload
FINEST: TDSChannel (ConnectionID:1) (SSLHandshakeInputStream): Reading first packet of SSL handshake response
May 26, 2025 3:42:31 PM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream readInternal
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@320de594 Reading 8 bytes
May 26, 2025 3:42:31 PM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream readInternal
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@320de594 Read 8 bytes
May 26, 2025 3:42:31 PM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream readInternal
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@320de594 Reading 1241 bytes
May 26, 2025 3:42:31 PM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream readInternal
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@320de594 Read 1241 bytes
May 26, 2025 3:42:31 PM com.microsoft.sqlserver.jdbc.TDSChannel logPacket
FINEST: /10.0.11.1:52593 SPID:0 TDSReader@1 (ConnectionID:1 ClientConnectionId: 268f2495-ac17-464a-a23f-5fd9e7fd1434) received Packet:1 (1241 bytes)
12 01 04 E1 00 00 00 00 16 03 03 04 D4 02 00 00   ................
51 03 03 68 34 6F C7 99 C8 28 18 9D B6 7C EE B0   Q..h4o...(...|..
77 10 33 D9 99 CE D4 95 C9 2B 5C D4 10 2F 45 F5   w.3......+\../E.
7F A1 BC 20 35 27 00 00 D3 2D 60 3F EA 1A 5E 93   ... 5'...-`?..^.
29 71 1F 9F 56 C2 EE F7 F0 19 B6 F5 63 BF 98 EC   )q..V.......c...
7B 04 29 BC C0 30 00 00 09 00 17 00 00 FF 01 00   {.)..0..........
01 00 0B 00 03 0A 00 03 07 00 03 04 30 82 03 00   ............0...
30 82 01 E8 A0 03 02 01 02 02 10 50 66 E4 BC 31   0..........Pf..1
66 77 9F 43 D0 16 4C E6 A5 7F F4 30 0D 06 09 2A   fw.C..L....0...*
86 48 86 F7 0D 01 01 0B 05 00 30 3B 31 39 30 37   .H........0;1907
06 03 55 04 03 1E 30 00 53 00 53 00 4C 00 5F 00   ..U...0.S.S.L._.
53 00 65 00 6C 00 66 00 5F 00 53 00 69 00 67 00   S.e.l.f._.S.i.g.
6E 00 65 00 64 00 5F 00 46 00 61 00 6C 00 6C 00   n.e.d._.F.a.l.l.
62 00 61 00 63 00 6B 30 20 17 0D 32 35 30 34 31   b.a.c.k0 ..25041
30 30 39 31 36 33 34 5A 18 0F 32 30 35 35 30 34   0091634Z..205504
31 30 30 39 31 36 33 34 5A 30 3B 31 39 30 37 06   10091634Z0;1907.
03 55 04 03 1E 30 00 53 00 53 00 4C 00 5F 00 53   .U...0.S.S.L._.S
00 65 00 6C 00 66 00 5F 00 53 00 69 00 67 00 6E   .e.l.f._.S.i.g.n
00 65 00 64 00 5F 00 46 00 61 00 6C 00 6C 00 62   .e.d._.F.a.l.l.b
00 61 00 63 00 6B 30 82 01 22 30 0D 06 09 2A 86   .a.c.k0.."0...*.
48 86 F7 0D 01 01 01 05 00 03 82 01 0F 00 30 82   H.............0.
01 0A 02 82 01 01 00 D0 F6 EF D0 7D 19 80 BB E8   ...........}....
5E 7E 27 7D 89 C3 9F B0 39 64 9D 21 DD B0 D1 64   ^~'}....9d.!...d
4E 08 60 8E 49 A8 FB 52 AC CC B5 0C 7C 33 CE 7E   N.`.I..R....|3.~
F3 9D C6 00 93 1B 82 7A 0D 79 B8 4B 6E D2 AC 3C   .......z.y.Kn..<
2E 02 90 F9 81 1C BE E5 A9 CE 01 3F 98 B5 EF D0   ...........?....
7F EC 59 1C B4 CD A1 FD B6 40 75 3C DC B6 96 44   ..Y......@u<...D
07 C1 3B DC A2 E1 FD E4 B1 C9 30 EE 06 56 48 E1   ..;.......0..VH.
E9 1F A2 F2 49 F5 14 DB F0 E9 4A B8 6D 75 13 D6   ....I.....J.mu..
B1 E5 A9 19 FD A9 D9 40 07 9F CC E3 A1 1B 21 34   .......@......!4
41 92 63 50 C6 FB CA 27 51 F7 D7 71 2A 96 00 F0   A.cP...'Q..q*...
9E 9A 17 79 30 4C 0A 94 DF 20 CF 09 F8 4E 44 3A   ...y0L... ...ND:
D5 6B 3A 03 3F F1 41 52 94 97 69 C5 10 66 E9 EF   .k:.?.AR..i..f..
60 9B 55 2D F8 C5 99 A8 27 01 8A 1A 15 16 E8 3F   `.U-....'......?
C9 93 79 D4 63 FE 4B 5C F4 15 26 06 14 5E AE 40   ..y.c.K\..&..^.@
E5 3A F4 90 B2 65 01 E0 06 00 E6 92 10 ED AD D1   .:...e..........
C7 7F A6 52 94 6B D4 A7 1A BB AF 87 CD 44 55 29   ...R.k.......DU)
AE EC EB ED 27 F8 F5 02 03 01 00 01 30 0D 06 09   ....'.......0...
2A 86 48 86 F7 0D 01 01 0B 05 00 03 82 01 01 00   *.H.............
CC 92 75 BF 5C 6B 54 90 60 AE D2 FF 71 2F AC EC   ..u.\kT.`...q/..
3D 28 B9 B2 A2 BC D0 56 C2 EF 0E 07 37 51 F6 FB   =(.....V....7Q..
60 5D CE A9 95 78 17 87 55 BC 14 75 67 C3 48 01   `]...x..U..ug.H.
69 95 57 40 A5 F3 1C 3A A2 CD A4 67 5D E3 BB C1   i.W@...:...g]...
87 9B 9C CF 34 CC 90 44 8B 1F F5 4E E8 77 94 E0   ....4..D...N.w..
10 B4 C2 24 C9 22 7C 3A 17 3A A4 32 BA 58 A5 18   ...$."|:.:.2.X..
BD A2 B8 5B 7B 05 98 AB CA 76 D7 0C 9D 18 E9 D1   ...[{....v......
B9 04 A2 96 45 24 92 3D 46 6C 84 2C 84 8B 9E E8   ....E$.=Fl.,....
1D DF 29 DB 67 76 64 9B D1 63 35 0A 5B 0C 15 32   ..).gvd..c5.[..2
8A 60 2A 97 AC CF A0 25 69 30 F9 D8 5A 50 E9 1F   .`*....%i0..ZP..
0E 33 83 42 55 A6 37 47 4C 01 98 0E 9A 1B 48 77   .3.BU.7GL.....Hw
BA 3F DB A0 F5 2C 4F 07 2E EC 3B F3 0C FC 21 33   .?...,O...;...!3
46 FB 38 92 AA 29 52 F6 C9 75 56 4A 71 2A CA 3F   F.8..)R..uVJq*.?
C1 D6 78 CE DE 7C EF 66 94 47 09 03 EF 93 88 AD   ..x..|.f.G......
CC 4B 61 E1 D2 DB 41 95 E9 4E 17 B7 74 F8 59 2D   .Ka...A..N..t.Y-
8D 10 87 4B 58 30 35 52 0E EF F8 68 92 05 DE 9A   ...KX05R...h....
0C 00 01 69 03 00 18 61 04 09 FF A0 4F C6 51 7F   ...i...a....O.Q.
F2 8A 0A 9F 6A C7 9E 65 3F DF BA DF A4 FA C8 B6   ....j..e?.......
7B 85 4F 97 4C 40 E9 71 57 8E 31 84 F7 75 8E 79   {.O.L@.qW.1..u.y
AE E4 A8 DB 59 EA 5B 15 5B C1 09 C1 81 E3 15 5A   ....Y.[.[......Z
D2 6F 96 6E 5C 83 F3 F5 AD 81 9C E4 03 C3 3E 1F   .o.n\.........>.
C2 FA 3A 5C D5 59 94 4D B9 1F 02 F2 A5 39 65 C2   ..:\.Y.M.....9e.
A9 4D 5C D0 0A 66 10 89 48 04 01 01 00 3B AB 5A   .M\..f..H....;.Z
A5 88 55 DC 48 61 39 D7 75 81 CA E3 4F DA CB 2A   ..U.Ha9.u...O..*
62 B0 AA 2E 93 D6 4F 16 55 5D 24 B2 2B BA 67 67   b.....O.U]$.+.gg
B9 6D 2B E5 08 A8 55 79 78 0A 51 BD 16 82 9B C1   .m+...Uyx.Q.....
EF 04 B6 87 20 01 EE 9E 05 13 4C 4F 87 CC 25 1C   .... .....LO..%.
87 01 80 19 31 C1 79 87 19 96 FC 71 69 DB 01 CA   ....1.y....qi...
F8 26 0A 38 ED 13 74 2E A5 AE 49 F3 C7 AD 08 AB   .&.8..t...I.....
BC 5C 65 A5 3C 8F 5C 35 95 8F 9B 13 38 7F B6 B0   .\e.<.\5....8...
3F C3 15 2A 2C 58 44 EC 4D 79 4C D4 BE 8F 62 F4   ?..*,XD.MyL...b.
63 CE 7F CD DB 3D 82 C2 78 22 32 0B 33 D6 5B 31   c....=..x"2.3.[1
2B C2 05 11 B5 F7 0E C3 D6 DD DD 71 D5 92 4C E6   +..........q..L.
22 7E 4E 80 31 75 B5 6A A6 A6 B1 6B 48 CD 04 A3   "~N.1u.j...kH...
60 51 F4 6E 81 2A 72 DC 56 CA 05 BD 30 21 72 3B   `Q.n.*r.V...0!r;
49 3C 17 1A CB E4 C7 21 DA 4C 8C 66 9E A8 9C BD   I<.....!.L.f....
4A 8F FB E0 89 A8 DF D0 64 66 55 13 7F 4A 68 68   J.......dfU..Jhh
72 3D CE 7C 68 D4 69 30 40 16 FC 7F 39 BA 55 D5   r=.|h.i0@...9.U.
BF 9B BC 7D 0D 6F 3D CA 6A D1 1F 7D 2B 0E 00 00   ...}.o=.j..}+...
00                                                .

Let me know if you are looking for specific information.

[David-Engel]

@jubax

You're not calling clearBatch() between executeBatch() calls. You are eventually building up to a 100,000,000 batch execution. Your loop looks like this:

Iteration 1 of executeBatch() contains:
    INSERT row1
Iteration 2 of executeBatch() contains:
    INSERT row1
    INSERT row2
Iteration 3 of executeBatch() contains:
    INSERT row1
    INSERT row2
    INSERT row3
....
Iteration 100,000,000 of executeBatch() contains:
    INSERT row1
    ... 99,999,998 more inserts in the batch
    INSERT row 100,000,000

The 100,000,000th iteration is going to have all the rows in memory to execute in the final executeBatch() call.

Performing batch operations - JDBC Driver for SQL Server | Microsoft Learn

David

[jubax]

@jubax

You're not calling clearBatch() between executeBatch() calls. You are eventually building up to a 100,000,000 batch execution. Your loop looks like this:

Iteration 1 of executeBatch() contains:
    INSERT row1
Iteration 2 of executeBatch() contains:
    INSERT row1
    INSERT row2
Iteration 3 of executeBatch() contains:
    INSERT row1
    INSERT row2
    INSERT row3
....
Iteration 100,000,000 of executeBatch() contains:
    INSERT row1
    ... 99,999,998 more inserts in the batch
    INSERT row 100,000,000

The 100,000,000th iteration is going to have all the rows in memory to execute in the final executeBatch() call.

Performing batch operations - JDBC Driver for SQL Server | Microsoft Learn

David

You are right, but I think you missed the "break" after the first iteration. The OOME is thrown in the very first iteration (in the first call to executeBatch()).

If we replace the "while" with an "if" to make it more obvious then we still run into the OOME:

System.out.println("insert into staging table ...");
try (
        PreparedStatement ps1 = connection.prepareStatement("""
                        select att1 as x1, att2 as x2 from large_table
                        union
                        select att1 as x1, att3 as x2 from large_table
                        union
                        select att1 as x1, att2 + att3 as x2 from large_table
                        union
                        select att1 as x1, att3 + att2 as x2 from large_table
                """);
        PreparedStatement ps2 = connection.prepareStatement("INSERT INTO staging (att1, att2) VALUES(?, ?);");) {

    try (ResultSet rs = ps1.executeQuery()) {
        if (rs.next()) { // <=== ONLY ONE ITERATION
            long id = rs.getLong(1);
            String uuid = rs.getString(2);

            // Add a single batch
            ps2.setLong(1, id);
            ps2.setString(2, uuid);
            ps2.addBatch();

            long t0 = System.currentTimeMillis();
            System.out.println("execute batch ...");
            // Executing this batch takes a long time while the heap memory is increasing to several GB.
            // => It seems the outer query is executed in the background
            ps2.executeBatch();
            System.out.println("execute batch ... done. Time: " + (System.currentTimeMillis() - t0) + " ms");
        }
    }
}

Thank you!

[machavan]

@jubax, can you change the second part of your code to use separate connections for ps1 and ps2 and share with us if it goes through?

[David-Engel]

@jubax

I actually didn't even get to the second loop, where the OOM occurs. Oops. The first loop is inefficient, yes, but not what you were complaining about. Sorry.

In the second loop, you are executing two statements on the same connection. The first statement, ps1, will have a result that is... well, I'll just call it "huge". 😄 You execute ps1, look at the first row, then execute ps2 on the same connection. That connection has "huge" results from ps1 waiting to be read from the server. The server can't execute ps2 on the connection until something is done with the results from ps1, so the driver has to read them since you didn't explicitly say to cancel them. (The driver can't just implicitly cancel ps1 because cancelling would result in different behavior than consuming the results.) It's that reading (or consuming) of the results that is resulting in the OOM error. You can either cancel the previous result (ps1.cancel()) before executing ps2 or, as @machavan suggested, use separate connections for ps1 and ps2.

Now, you could argue that in your scenario the driver should simply discard everything it reads instead of holding it in memory, and you might be right. We'll look into that part. We'll have to see if there are any valid scenarios that actually need the data. (We might need to check for any open streams before discarding, as those should remain valid until they are freed but I'm not sure what other reasons there might be to NOT just discard the data.)