Help with blocking on querying two joined tables

[loosecannon_1]

I get a 90-120 second blocking when send 15 or so simultaneous queries
to SQL Server 2000 that query a view made up of two joined tables.
After each query is blocking for the same amount of time they all
return. Further identical queries of this type work in 3-4 seconds
(caching?) until hours later where it happens again. If I query the
tables directly (without the view) I still get the same blocking. If I
remove the join (it is a simple inner join on two columns) I do not get
the blocking.

Any ideas?

[Erland Sommarskog]

lo***********@yahoo.com (lo***********@yahoo.com) writes:

I get a 90-120 second blocking when send 15 or so simultaneous queries
to SQL Server 2000 that query a view made up of two joined tables.
After each query is blocking for the same amount of time they all
return. Further identical queries of this type work in 3-4 seconds
(caching?) until hours later where it happens again. If I query the
tables directly (without the view) I still get the same blocking. If I
remove the join (it is a simple inner join on two columns) I do not get
the blocking.

Wait here, if the processes only queries the view, there can't be any
blocking. It's another issue, of course, if there is a process that
performs an update these tables.

How do you conclude that actually have blocking? Did you run sp_who,
and saw that these processes had a non-zero value in the Blk column?

Or did you just think that the queries took a long time to run?

Caching could indeed be part of it. SQL Server keeps a lot of the data
in the cache, since reading from main memory is a lot faster than reading
from disk. If these tables are queried frequenly they should stay in cache.
But there are a few things that may force them out of the cache:
1) Another query runs and drags some big table into memory.
2) Some performs an operations that flushes the caches, for instance
DBCC DROPCLEANBUFFERS, or restart of SQL Server.

Since the queries takes 3-4 seconds to run from cache, maybe there is
idea to review indexing to speed them up.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[loosecannon_1]

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. They all take 120 seconds or they all take 90. I did not run
sp_who because I was running then from a web client via JDBC. 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.

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

[Erland Sommarskog]

lo***********@yahoo.com (lo***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx