Identifying db locks

performance monitor -> locks objects
profiler
might be caused by I/O bottleneck
try to use index for update and delete
SP_INDEXOPTION : page lock, row lock, table lock ...etc
use nolock for select (dirty read)

sp_lock
sp_who/sp_who2

--shwo long running process--
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000 --The wait time is measured in milliseconds
AND spid > 50
--