Another item to check out is the complexity of the stored procedure. If you have a stored procedure that takes a five seconds to compile, this issue could simply indicate that the stored procedure was not in the proc cache and needed to be compiled.


JONATHON3/25/2005 9:33:20 AM

We experienced exactly the same issue a few years ago. The issue was due to the complete serialisation of queries containing #temp tables. SQL 7 and 2000 (at least upto SP2) generate exclusive locks on certain system tables during temp table creation. This then leads to all other instances of the same query queuing until the temp tables have finished being used. Unless the offending queries are performing updates to the same data and thus being blocked by that, I would strongly look at use of #temp tables as a possible culprit. #Temp tables very often do not scale at all.

Dan2/4/2005 7:41:18 AM