Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Intermittent locking

Author  Topic 

LopakaB
Starting Member

22 Posts

Posted - 2011-11-16 : 13:31:39
I have a simple query:
SELECT *
FROM tbl1 INNER JOIN
tbl2 ON tbl1.ID=tbl2.ID INNER JOIN
tbl3 ON tbl3.ID2=tbl1.id2
It takes > 4 min to run so when I looked with sp_who I noticed it was locking... I ran sp_who a few times consecutively and notice i was locking itself??? Anybody have any idea, anything would help... :)

Lopaka


Lopaka

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-16 : 13:35:07
Probably spawned parallel threads and was waiting for one to finish.
If you look at master..sysprocesses you whould see all the threads and can see which are processing and which are waiting.

The idea is that the optimiser spots that it can execute parts of the query independently so spawns threads to do that. The main thread waits until the data is available from the other threads so it can carry on, until then it looks like it's blocked by itself.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -