We feel the same pain here. Ours is a web app using Tomcat 5.0.28, Hibernate 3, SQL Server 2000 and jTDS JDBC driver. We experienced very bad performance but stumbled upon a work-around. We pounded the SQL Server through the web app by JMeter and then run
sp_MSForEachTable 'Update Statistics ? with FullScan'
And all of a sudden, the SQL Server was flying again.
However, this even puzzled me more as I observed the thing as you do. In raw SQL, the performance was great. It was wrong only through the app, i.e. through Hibernate. And the cpu utilization of SQL server went sky high when the performance was wrong. But if it's Hibernate that was wrong, why would SQL server CPU went sky high. It should be the Tomcat's cpu went sky high! Right?
And one final observation was that the SQL server didn't perform whenever it was rebooted. I have to pound it and run update statistics before it would fly again.
Can anyone solve this mystery?