Posted by Erland Sommarskog on 07/06/07 22:24

(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Help writing an If else statement
• Prev in forum: Re: SSMS - getting timeout terminated even with Execution time-out of 0?
• Thread view: Re: Problem with locking in Selects

[Reply to this message]