Answer by Christian Strebel

Christian Strebel — Wed, 07 Sep 2016 09:29:56 -0400

You can configure the limit if a big clob or blob data is stored in a temporary file with the additional connection property lobBuffer which is set to "32768" (32kb) by default. Means by default every clob or blob data which is bigger than 32kb is stored as an temorary file. Our recommendation is to increase lobBuffer to 524288 (512kb) or even more, but not too big if you have not enough memory. Fore a good value which suits your needs you can look at the file size of the jdtsXYZ.tmp files already created.

For other performance improvements you could also increase the follwoing properties:

bufferMaxMemory default value 1024 in kb (1mb): Controls the global buffer memory limit for all connections (in kilobytes).
packetSize default 4096 or 512 in bytes: The network packet size (a multiple of 512).
bufferMinPackets default 8 packets (8 * packed size): Controls the minimum number of packets per statement to buffer to memory.

For more detailed description of the properties see jTDS driver options

Be careful to not increase this values too much, because you then need a lot of memory! Be aware that you have per default a maximum of 50 connections to the system database!

You can configure this additional connection properties with the "Engine Configuration" tool: or directly in the serverconfig.xml:

<serverConfiguration>
    <systemDatabase>
        <driverName>net.sourceforge.jtds.jdbc.Driver</driverName>
        <connectionUrl>jdbc:jtds:sqlserver://...</connectionUrl>
        <username>...</username>
        <password>...</password>
        <property>
            <name>bufferMaxMemory</name>
            <value>102400</value>
        </property>
        <property>
            <name>lobBuffer</name>
            <value>524288</value>
        </property>
        <property>
            <name>packetSize</name>
            <value>4096</value>
        </property>
        <property>
            <name>bufferMinPackets</name>
            <value>32</value>
        </property>
    </systemDatabase>
</serverConfiguration>

Answers to: How to improve performance of MSSql system database with jTDS driver (many temporary files)

Answer by Christian Strebel