Posted by Erland Sommarskog on 12/08/05 22:43

loosecannon_1@yahoo.com (loosecannon_1@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, 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]