I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.

The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.

I have also checked if there are any locks holding the other SPs back
- there isn't any.

So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?


Regards

Bjørn

(bjornsuneandersen@gmail.com) writes:
> I have a Stored Procedure that performs a simple SELECT. The Select
> have no locking hints or other hints and the database is set up in a
> standard configuration.
>
> The problem is that the SELECT runs for some time and while it is
> running I can see (in the profiler) that other SPs with simple SELECTs
> are held waiting until "my" SP has finished. The other SPs may be
> other instances of the same SP as the one I'm running. All SPs
> contains simple SELECTs and should only hold shared locks.
>
> I have also checked if there are any locks holding the other SPs back
> - there isn't any.
>
> So my question is: What resouce can hold out other simple SELECTs in
> this situation? Where should I look to identify the resource?

I have written a stored procedure aba_lockinfo which is useful for
this sort of things. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html

What you should look for is the value WAIT in the lstatus column. That is
what the blocked processes are waiting for.


--
Erland Sommarskog, SQL Server MVP, esquel@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