Microsoft JDBC Driver Performance Degradation

We have been using Microsoft SQL Server JDBC Driver 2.2.0040 in our products
to connect to SQL Server 2000 Standard Edition SP3 (8.00.760). Our
functional testing went fine but we have run into serious performance
degradation under simulated heavy loads.
Our server crashed with OutOfMemory errors due to the inability to create
new native threads for the BaseQueryTimeoutEnforcer class in the JDBC driver
library. Right before crashing, we saw high thread counts went from 150 to as
high as 490. Thread counts were variable, so the thread count would decrease
and increase over time, until the last thread count reported 489 just before
crashing.
Total GC time was much higher as well due mostly to incremental GC times
that were almost 3x that of the benchmarked WebLogic's jDriver.
Correspondingly, response times were much higher.
I wonder if anyone else has experienced such problems.
Thanks,
Brian Luo
Brian Luo wrote:

> We have been using Microsoft SQL Server JDBC Driver 2.2.0040 in our products
> to connect to SQL Server 2000 Standard Edition SP3 (8.00.760). Our
> functional testing went fine but we have run into serious performance
> degradation under simulated heavy loads.
> Our server crashed with OutOfMemory errors due to the inability to create
> new native threads for the BaseQueryTimeoutEnforcer class in the JDBC driver
> library. Right before crashing, we saw high thread counts went from 150 to as
> high as 490. Thread counts were variable, so the thread count would decrease
> and increase over time, until the last thread count reported 489 just before
> crashing.
> Total GC time was much higher as well due mostly to incremental GC times
> that were almost 3x that of the benchmarked WebLogic's jDriver.
> Correspondingly, response times were much higher.
> I wonder if anyone else has experienced such problems.
> Thanks,
> Brian Luo
Hi. For the MS driver, you should avoid using setQueryTimeout() in middleware
because it will spawn a separate thread to enforce that for every statement.
This will play havoc with a JVM that is trying to multiplex a fixed number
of system threads to do enterprise-level loads and work.
It is certainly possible that MS's next driver version will fix this issue
but it doesn't hurt to ask MS to verify that...
For a WebLogic environment, is there any reason not to use WebLogic's
drivers? Particularly for our 8.1 versions, you will get a thoroughly superior
driver for MS SQLServer. I do understand that in our older WebLogic versions,
our jDriver is efficient, but frozen at the JDBC 1.0 level. If you cannot upgrade
to 8.1, the easiest fix is to use a third-party driver. I would recommend
DataDirect.
Joe Weinstein at BEA
|||Thanks. It seems a rather inefficient way to implement query timeout
mechanism at per statement level. It could be done either at per connection
level or even better, only one timeout thread per JVM.
Ironically, the 3.0 DataDirect driver had a similar problem . Not a surprise
because MS and DataDirect drivers are of the same origin. We worked with BEA
to get the problem fixed in the 3.3.0047 version.
The primary reason for us not using DataDirect (or other third party driver)
is business reason. Also, using drivers embedded in WebLogic/WebSphere impose
various restrictions for our product.
Your suggestion is a good one though. We should avoid to setQueryTimeout()
whenever possible. For example, if it is already 0 (default), we should avoid
setting it.
Thanks,
Brian
"Joe Weinstein" wrote:

> Brian Luo wrote:
>
> Hi. For the MS driver, you should avoid using setQueryTimeout() in middleware
> because it will spawn a separate thread to enforce that for every statement.
> This will play havoc with a JVM that is trying to multiplex a fixed number
> of system threads to do enterprise-level loads and work.
> It is certainly possible that MS's next driver version will fix this issue
> but it doesn't hurt to ask MS to verify that...
> For a WebLogic environment, is there any reason not to use WebLogic's
> drivers? Particularly for our 8.1 versions, you will get a thoroughly superior
> driver for MS SQLServer. I do understand that in our older WebLogic versions,
> our jDriver is efficient, but frozen at the JDBC 1.0 level. If you cannot upgrade
> to 8.1, the easiest fix is to use a third-party driver. I would recommend
> DataDirect.
> Joe Weinstein at BEA
>
>
|||Brian Luo wrote:

> Thanks. It seems a rather inefficient way to implement query timeout
> mechanism at per statement level. It could be done either at per connection
> level or even better, only one timeout thread per JVM.
> Ironically, the 3.0 DataDirect driver had a similar problem . Not a surprise
> because MS and DataDirect drivers are of the same origin. We worked with BEA
> to get the problem fixed in the 3.3.0047 version.
> The primary reason for us not using DataDirect (or other third party driver)
> is business reason. Also, using drivers embedded in WebLogic/WebSphere impose
> various restrictions for our product.
> Your suggestion is a good one though. We should avoid to setQueryTimeout()
> whenever possible. For example, if it is already 0 (default), we should avoid
> setting it.
Glad to help. By JDBC spec, it *is* zero by default.
Joe Weinstein at BEA
[vbcol=seagreen]
> Thanks,
> Brian
> "Joe Weinstein" wrote:
>
|||You are absolutely correct. And that's why I said we should avoid setting it
if the value is 0 (in our product).
Thanks,
Brian
"Joe Weinstein" wrote:

>
> Brian Luo wrote:
>
> Glad to help. By JDBC spec, it *is* zero by default.
> Joe Weinstein at BEA
>
>
|||Brian Luo wrote:

> You are absolutely correct. And that's why I said we should avoid setting it
> if the value is 0 (in our product).
Ok, but I can't imagine that the driver is so broken as to spawn a thread if
setQueryTimeout() is called with a zero value. I believe the problem only occurs
when there is a selected query timeout, and my only hope is that you can
limit any of your customers from causing it to be called with any non-zero
value until MS provides a free driver that has the same sort of fix as the
one you got through BEA.
Joe
[vbcol=seagreen]
> Thanks,
> Brian
> "Joe Weinstein" wrote:
>
|||I didn't really believe it but it is true, unfortunately. We have a parameter
in our product to set query timeout value. In our performance tests, we
always set it to 0. With WebLogic's jDriver, we never had any problem. But
with MS or older version of DataDirect, we always ran into OOM errors with
BaseQueryTimeoutEnforcer.
Thanks,
Brian
"Joe Weinstein" wrote:

>
> Brian Luo wrote:
>
> Ok, but I can't imagine that the driver is so broken as to spawn a thread if
> setQueryTimeout() is called with a zero value. I believe the problem only occurs
> when there is a selected query timeout, and my only hope is that you can
> limit any of your customers from causing it to be called with any non-zero
> value until MS provides a free driver that has the same sort of fix as the
> one you got through BEA.
> Joe
>
>
|||Brian Luo wrote:

> I didn't really believe it but it is true, unfortunately. We have a parameter
> in our product to set query timeout value. In our performance tests, we
> always set it to 0. With WebLogic's jDriver, we never had any problem. But
> with MS or older version of DataDirect, we always ran into OOM errors with
> BaseQueryTimeoutEnforcer.
> Thanks,
> Brian
Yow! Well, ok then. The bug is even worse, but we at least have a partial workaround...
thanks for the heads-up.
Joe
[vbcol=seagreen]
> "Joe Weinstein" wrote:
>
|||The Microsoft SQL Server 2000 JDBC driver doesn't spawn a new thread if
the query timeout is left at its default value (zero). However, if
setQueryTimeout is set -- even to explicitly set the value to zero --
then a thread will be spawned.
We will make the fix to have the driver never spawn a new thread in the
zero case -- default or explicit.
-shelby
Shelby Goerlitz
Microsoft SQL Server
"Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
news:joeNOSPAM@.bea.com:[vbcol=seagreen]
> Brian Luo wrote:
>
>
> Yow! Well, ok then. The bug is even worse, but we at least have a partial
> workaround...
> thanks for the heads-up.
> Joe
>
>
>
>
>
>
>
|||The timer threads are not necessary. TCP/IP timeouts could be used just
as well. Other JDBC drivers do it. At the least, what could be done is
have a single timeout thread throughout the app. That thread could then
interrupt any of the working threads.
Alin,
The jTDS Project.