Posted by Erland Sommarskog on 10/14/58 11:34

joshsackett (joshsackett@gmail.com) writes:
> The entire gist is this: The application(s) run the SQL calls as a
> standard
>
> It is SQL Server performing all of the cursor work; I'm guessing
> through whatever translation layer. Maybe JDBC is causing this? Has
> anyone seen this before?

I have not worked with JDBC. But server-side cursors are not unheard of.
It is the default for ADO, for instance. (While ADO .Net does not have
them!)

> My second problem is that I let the application run for 1 day in user
> acceptance testing and I ended up with 1.8GB procedure cache (all
> cursors) at 50% cache hit ratio and 140MB of data cache at 99% cache
> hit ratio. What could be the cause?

This can happen with applications that generate SQL on the fly, instead
of using stored procedures. You get a lot of queries in the cache, and
that's not really good, as the chance for reuse is quite small. On top
of that, you get hash collitions in the cache, so you get a delay when
a query is submitted, as SQL Server looks to see if it is already in the
cache. This can be very painful on servers with 25 GB of memory.

The fact that the data cache is 140 MB and has a high hit ratio, indicates
that the queries are quite confined in which data they access. Probably
a good thing.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]