What is adaptive response buffering and why should I use it?

Do you currently use selectMethod=cursor to work around OOM errors with very large result sets?  Do you find that the driver seems to consume quite a lot of memory for forward only read only result sets?  Does it seem like it takes a long time to return from Statement.executeQuery for simple SELECT statements that return many rows?  Do you wish you could get that 600MB LOB you streamed into a column back out again without needing loads of memory to avoid an OOM?  If you answered 'yes' to any of these questions, then you'll want to use adaptive response buffering.

Adaptive response buffering is a new feature of the v1.2 JDBC driver that allows the driver to reduce memory usage and statement execution latency.  With adaptive buffering, the driver retrieves statement execution results from SQL Server as the application needs them, rather than all at once.  The driver also discards results as soon as the application can no longer access them.

By default, adaptive response buffering is turned off so that the v1.2 driver is 100% backward compatible with the v1.0 and v1.1 drivers.  To use adaptive response buffering, you should add "responseBuffering=adaptive" to your connection URL or use the DataSource method SQLServerDataSource.setResponseBuffering("adaptive").  If you need finer control, at the statement level, you can also downcast any Statement (or PreparedStatement or CallableStatement) instance returned by the driver to a SQLServerStatement and call SQLServerStatement.setResponseBuffering("adaptive").

Ok, but what does adaptive response buffering do?

First, it helps to understand what full buffering (the default, backward-compatible behavior) does.  For large results, full buffering trades off use of increased application memory usage and longer perceived statement execution latency against reduced lock contention in SQL Server.  For example, by retrieving all the rows of a forward only, read only result set up front, the driver allows the server to relinquish table read locks that may block updates.  But to do that, it must buffer all of the rows in memory.

But for very large result sets, a fully-buffered strategy may be infeasible. Let's say you execute a SELECT statement that returns a million rows.  Adaptive response buffering allows you to process those rows through a forward only, read only result set, without incurring the overhead of a server cursor (selectMethod=cursor) and without requiring the driver buffer more than one row.  With adaptive response buffering, the driver reads row data from the database as the application traverses the result set.

Now let's say that some of the selected column values are large -- really large -- like a 600MB LOB.  Buffering the LOB may be infeasible.  Random access to the LOB value through the Blob/Clob interfaces currently requires the whole LOB to be buffered, but it is possible to stream the entire LOB value out through an InputStream or Reader obtained through one of the ResultSet methods: getBinaryStream, getCharacterStream, or getAsciiStream.  With adaptive buffering, accessing LOB data through a stream requires only a small fixed amount of memory.  The only restriction is that with adaptive buffering, the LOB value can be streamed out only once.  If your application needs to re-read any portion of the value, it must call the mark method on the InputStream or Reader to start buffering data that is to be re-read after a subsequent call to the reset method.

What about large CallableStatement OUT parameters?

The JDBC CallableStatement interface does not include getBinaryStream, getCharacterStream or getAsciiStream methods.  But since accessing LOB values through the Blob/Clob interfaces still require the whole LOB to be buffered, these stream getter methods have been added to the SQLServerCallableStatement class.  Any CallableStatement instance returned by the v1.2 driver can be downcast to a SQLServerCallableStatement instance to allow use of these methods.

How the application accesses data is important

How much benefit an application gets from adaptive response buffering is determined not by the size or type of the data accessed, but by how the application accesses the data.  For example, putting a large binary column after other columns in the select list, and then accessing it using getBinaryStream rather than getBytes, allows the driver to avoid buffering the value.  But if the large binary column appears somewhere in the middle of the select list, the application would need to stream it out before accessing the columns that follow it to keep the driver from buffering it.

 

I hope this helps explain the basics of the new adaptive response buffering feature.

David Olix, SQL Server
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

• Anonymous
  September 25, 2007
  PingBack from http://ibrahimdemir.wordpress.com/2007/09/26/sql-server-jdbc-driver-12-ctp/

• Anonymous
  January 24, 2008
  My experience using this new setting do not seem to square with its description. I altered my connection to use selectMethod=direct and responseBufering=adaptive. When I executed my query and watched my console output and Task Manager network activity I saw my "exec query" println, then several minutes of sustained incoming data, then ...processing N records..." as my loop read the data. While the loop was running there was no network activity. I would have expected to see network activity concurrent with moving through the result set. What am I missing?

• Anonymous
  January 25, 2008
  Do you have a typo in your connection string?  The responseBuffering connection property is spelled with two 'f's. David Olix, SQL Server

• Anonymous
  March 09, 2008
  Does responseBuffering option work also when connecting to SQL Server 2000?

• Anonymous
  May 07, 2008
  it works perfectly for me. I could get a 400000 records query without any Out of Memory errors. THANK YOU.

• Anonymous
  May 08, 2008
  The comment has been removed

• Anonymous
  May 08, 2008
  The comment has been removed

• Anonymous
  May 08, 2008
  Thanks foir the quick reply. here is the code snippet for how I create a DB connection and how to do a query, but if I select a 100M blob, I still see 100M drop of free memory (you need to start JVM with -ms500m -mx500m), I will appreciate if you could suggest if I should do it differently, and if not if you can spend sometime to reproduce it, Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection conn = DriverManager .getConnection("jdbc:sqlserver://" + dbServer+";user="+user + ";password="+password +";databaseName="+ dbName +";responseBuffering=adaptive"); System.gc(); long  m1 = Runtime.getRuntime().freeMemory(); System.out.println(" the memory before is  :" +  m1)); PreparedStatement ps2 = conn.prepareStatement(" select c2 from dbo.table1 where c1=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ps2.setFetchSize(1); ((SQLServerStatement ) ps2).setResponseBuffering("adaptive"); ps2.setInt(1, number); ResultSet rs = ps2.executeQuery(); ps.close(); // cause server call of "unprepare" System.gc(); long  m2 = Runtime.getRuntime().freeMemory(); System.out.println(" the memory after select is  :" +  (m2)  ); InputStream inc = null; if( rs.next() ) { inc = rs.getBinaryStream("c2"); (); }

• Anonymous
  May 27, 2008
  I am finding that the adaptive buffering returns rows at the same rate as the server side cursor.  It may improve memory on large resultsets but I cannot seem to get performance up to the level of a similar app written in .NET using SqlDataReaders.  The JDBC performance is really bad. My app creates 6 threads, each with their own connection to the same SQL Server instance query different tables with the same replicated data.  The resultset will return 83,000 rows of 17 columns of data (a good mix of data types...no blobs).  The app will loop through every row and column but do nothing with the data, except reading it. JDBC averages 79 seconds .NET averages 0.90 seconds Any ideas? Here is a snippet of the java thread code: Connection c = DriverManager.getConnection("jdbc:sqlserver://" + host +               ":1433;databaseName=" + database + ";user=" + user + ";password=" + password +               ";responseBuffering=adaptive;"); long start = System.currentTimeMillis(); Date startDate = new Date(start); //  Statement stmt = c.createStatement(); Statement stmt = c.createStatement(SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY,              SQLServerResultSet.CONCUR_READ_ONLY); ((SQLServerStatement) stmt).setResponseBuffering("adaptive"); ResultSet rs = stmt.executeQuery(sql); rs.setFetchSize(10000); ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); String tempVar; int rowCounter = 0; // loop through rows in resultset while (rs.next()) {  // loop all columns and read value  for (int i=1; i<numberOfColumns; i++) {  tempVar = rs.getString(i);  }  rowCounter += 1; } Is the JDBC code that much different at the TDS protocol than .NET? Thanks

• Anonymous
  August 25, 2008
  Hi I have a scenario where I send several queries to sql server simultaniously (processed in different threads). OOM problem dissapears when I execuite only one query but resurface when more are beeing executed in paralell. Questions: what is the size of the client side buffer? Can we control the size of the client side buffer? TIA Knut

• Anonymous
  August 26, 2008
  Are you using adaptive buffering or full buffering? With full buffering there is no limit to the client cache and you cannot control the size of the cache.

• Anonymous
  August 26, 2008
  The comment has been removed

• Anonymous
  August 27, 2008
  Adaptive buffering implies it buffers it absolutely has to. The size of the buffer is not limited or controlled in anyway. The problem is that when you have several readers working off a single connection, we are forced to buffer the reply from the previous requests to get the data for the current request. There are few ways of solving this.

1. Using server cursors.
2. Using multiple connections instead of a single connection. However you may not want to create too many connections.

• Anonymous
  August 27, 2008
  Hi thanks for your answer. Just to be clear, I have one connection and one statement per thread. But all these connections will compete over the same available memory. So if SQL server can deliver rows faster than I can process them I will se the OOM error at some point? So how is memory size used for buffering contolled in this scenario? What's the adaptiv part? Thanks Knut

• Anonymous
  August 27, 2008
  The comment has been removed

• Anonymous
  August 28, 2008
  Ok but still the driver allocates memory to hold some rows from the result set? Do you know how much memory it will consume?

• Anonymous
  October 20, 2008
  Does responseBuffering option works when connecting to SQL Server 2000?

• Anonymous
  February 04, 2014
  First hit on google. I spent 30 seconds to read and then 30 seconds to post this comment. Then I will leave. Too much text that doesn't answer the questions in the title. "What is adaptive response buffering and why should I use it?" is still unanswered.