Posted by Erland Sommarskog on 12/09/05 21:32

loosecannon_1@yahoo.com (loosecannon_1@yahoo.com) writes:
> I believe it is blocking because I can send 12 or so via JDBC and they
> all sit there waiting and then all return within milliseconds of each
> other. It is not like one takes a long time and the others are real
> fast.

Well, if all 12 processes access the same data, and that data is not in
the cache, all 12 will have to wait until that memory has been read.

> I did not run sp_who because I was running then from a web client via
> JDBC.

I don't see how that prevents you from using sp_who to investigate
blocking. For a more completely view of the situation, you can use
aba_lockinfo, which you get from my web site:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

> The same behavior occurs whether I query the view or I query the tables
> directly with the join. I can query once that returns quickly and then
> query all 12 or so and they return in 3-4 seconds, which makes me think
> it is either a caching issue or something to do with running 12
> simultaneous queries on the same table. The caching is problematic
> because I would suspect one table to take a while and the rest to happen
> quickly unless there is some delay I am not aware of. I will say that
> these tables are replicated which I suspected make be an issue but I
> have other tables that are just as large (15 yrs worth of data) and are
> replicated and they have no problems. The ONLY difference is the join.

It's not that a table is in the cache either as a whole or not at all.
What SQL Server caches is pages. Thus, if you have a table with 15 years
of data, supposedly most references are to recent data, and thus only
some pages are in the cache. Now, if your query accesses the entire
table for some reason - for instance because of a poor query plan - there
could be a lot that needs to enter the memory.

Anyway, it could help, if you posted:

o CREATE TABLE statements for the tables, with size indications.
o CREATE INDEX statments for the tabels.
o The very query itself.

> I guess I can look into how I can increase the cache size and see if
> that helps.

By default, SQL Server grabs as much memory there is available. Unless
you can constrained how much memory SQL Server can use, the only way
to increase the cache is to buy more memory.

--
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]