locked
Microsoft JDBC Driver (4.2 & 6) - Prepared Statements with only a single query leak memory on server RRS feed

  • Question

  • Microsoft JDBC Driver (4.2 & 6) - Prepared Statements with only a single query leak memory on server

    This is an issue only when (selectMethod=direct).

    The following Java Code generates a connection that constantly uses more and more memory, until either the loop runs out or the server runs out of resources.

    If executeQuery is called twice on the Prepared Statement, then there is no memory leak. See commented out lines

    Activity Monitor on SQL Server can be used to see the memory slowly rising

    Is this something I have coded wrong or an actual problem.

    SQL Server versions tested

    • 2008 R2 (10.50.6529.0)
    • 2012 (11.0.5634.1)
    • 2014 (12.0.4422.0)

    import java.sql.*;
    
    public class BugTester {
    	public static String buildURL () {
    		String myURL;
    
    		myURL = "jdbc:sqlserver://<server>";
    		myURL += ";database=<database>";
    		myURL += ";selectMethod=direct";
    		myURL += ";responseBuffering=adaptive";
    		myURL += ";lastUpdateCount=true";
    		myURL += ";encrypt=false";
    		myURL += ";sendStringParametersAsUnicode=false";
    		myURL += ";disableStatementPooling=true";
    
    		System.out.println(myURL);
    	
    		return myURL;
    	}
    
    	public static void main (String[] args) {
    		Connection c = null;
    		PreparedStatement pstmt = null;
    		ResultSet rs = null;
    
    		try {
    			Class.forName ("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    			
    			c = DriverManager.getConnection (buildURL (), "sa", "password");
    
    			for (int i = 0; i < 10000; i++) {
    				pstmt = c.prepareStatement("SELECT 1");
    				try {
    					rs = pstmt.executeQuery ();
    					rs.close ();
    
    //					rs = pstmt.executeQuery ();
    //					rs.close ();
    
    					pstmt.close ();
    				} finally {
    					try { if (null != rs) { rs.close (); } } catch (SQLException e3) { e3.printStackTrace(); }
    					try { if (null != pstmt) { pstmt.close (); } } catch (SQLException e4) { e4.printStackTrace(); }
    				}
    				
    				try {
    					Thread.sleep(100);
    				} catch(InterruptedException ex) {
    					Thread.currentThread().interrupt();
    				}
    			}
    
    			c.close ();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} catch (Exception e1) {
    			e1.printStackTrace();
    		} finally {
    			try { if (null != rs) { rs.close (); } } catch (SQLException e6) { e6.printStackTrace(); }
    			try { if (null != pstmt) { pstmt.close (); } } catch (SQLException e7) { e7.printStackTrace(); }
    			try { if (null != c) { c.close (); } } catch (SQLException e5) { e5.printStackTrace (); }
    		}
    	}
    }


    Raised (https://connect.microsoft.com/SQLServer/feedback/details/2280301)

    Monday, January 18, 2016 6:54 AM

All replies

  • Amazing. Let me try it.
    Wednesday, January 20, 2016 8:52 AM
  • Anyway it make sense to just close your connection in finally block so first think I will do is

    try 
    { 
       c.close (); 
    } catch (SQLException e5) 
    { 
       e5.printStackTrace (); 
    }

    You should get the reading on how much RAM increases each time you run this. Don't forget JVM is also playing a role in your Java program.

    chanmm


    chanmm

    Thursday, January 21, 2016 2:43 AM
  • I am seeing the same problem. When my application uses sqljdbc42.jar memory usage for one of connections is constantly increasing. But when I used sqljdbc4.jar - this problem disappeared.

    I did SQL trace to see the difference, and I see that in case of sqljdbc4.jar, sp_prepexec is always accompanied with sp_unprepare call, but this is not the case for 42.jar. For some queries there is sp_unprepare but for others I do not see it.

    I am not sure whether sp_unprepare is necessary and whether it is related to memory leak, but this looks suspicious.

    Thursday, January 21, 2016 8:27 PM
  • The connection is closed in the finally block

    try { if (null != c) { c.close (); } } catch (SQLException e5) { e5.printStackTrace (); }

    The RAM on the client machine is not the problem. It is the RAM used by the session within SQL Server, as it does not call sp_unprepare ever and therefore runs out of RAM on the server after long enough.

    Monday, January 25, 2016 12:29 AM
  • Hi Lawrence Sim,

    Based on my test, it is exactly as you said. Thank you for providing feedback, it might be beneficial to other community members. Your cooperation and fully support will highly be appreciated.


    Sam Zha
    TechNet Community Support

    • Proposed as answer by Peter Klauser Friday, February 19, 2016 1:57 PM
    • Unproposed as answer by Peter Klauser Friday, February 19, 2016 1:57 PM
    Monday, January 25, 2016 1:06 PM
  • I believe that i'm having this same problem using Java1.8 and the Microsoft 4.2 JDBC driver. My application is slowing down over the period of hours and days and the session's memory on the server side is increasing into the 100's of megabytes. I have JDBC connection pooling in place so each of the jdbc connections is kept alive whilst in use. I did not have the problem with the 4.1 driver.

    I added my feedback to https://connect.microsoft.com/SQLServer/Feedback/Details/2280301 . Thank you for entering this bug.


    Friday, February 19, 2016 2:05 PM
  • Just in case anyone else stumbles on this thread like I did - here is my tuppence

    I was trying to find out why a 3rd party application was sometimes showing enormous (> 12 second) latency when paging though web pages. It runs on weblogic and uses jdbc to connect to a SQL Server backend.

    We had recently (ish) moved the SQL backend from a physical server to a VM so we could use zert0.

    Things seemed to be fine for a few weeks after the cutover but eventually we started getting problem reports of slowness for this one application - the database server wasn't under load and everything was a mystery ....

    Until I found this: https://communities.vmware.com/thread/524842?start=0&tstart=0

    Wasn't exactly our situation but it was very close - and yes, the netsh command suggested in that thread fixed it.

    netsh int tcp set global rsc=disabled

    Basically it seems that (bug may be fixed after the VMWare May 2016 update)

    JDBC -> SQL Server (version after 2012) -> On Windows Server 2012 -> hosted on VMWare -> using vmxnet3

    has a nasty bug that sometimes under certain load conditions slows the SQL network responses to a crawl

    This may have nothing to do with this thread - but I ended up here looking for a solution to my problem and I doubt I'm the only one who has seen this issue - so just in case someone else ends up over here.

    Thursday, July 28, 2016 7:06 PM
  • Hi,

    Have you tried the same code with jdts JDBC driver ?

    http://jtds.sourceforge.net/

    In my opinion, it's far more stable than MS one.

    Thursday, July 28, 2016 7:55 PM