Discover exactly what is causing a block

Hi. We are experiencing significant prolonged blocking when loading multiple
files in through a partitioned view. Before we start working on a resolution
we want to identify exactly what resource is causing the block.
We can see which processes are being blocked by using sp_who2. But how do we
determine exactly which lock on which object is causing the blocking?
Thanks!
McGy
[url]http://mcgy.blogspot.com[/url]sp_lock2 (it's out there on the internet) will tell you by spid which
database resources are being locked up.
This will be a good starting point.|||Also take a look at aba_lockinfo written by SQL Server MVP Erland
Sommarskog
Source code is available from the link below
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Denis the SQL Menace
http://sqlservercode.blogspot.com/
McGy wrote:
> Hi. We are experiencing significant prolonged blocking when loading multip
le
> files in through a partitioned view. Before we start working on a resoluti
on
> we want to identify exactly what resource is causing the block.
> We can see which processes are being blocked by using sp_who2. But how do
we
> determine exactly which lock on which object is causing the blocking?
> Thanks!
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]|||> We can see which processes are being blocked by using sp_who2. But how do
> we determine exactly which lock on which object is causing the blocking?
1) Execute sp_lock for the blocked spid. For example:
EXEC sp_lock 51
2) Identify the row with status WAIT and note the ObjId and indid
3) Determine the object/index
USE MyDatabase
SELECT
OBJECT_NAME(id) AS TableName,
name AS IndexName
FROM sysindexes
WHERE
id = 10000 --ObjId from sp_lock
AND indid = 1 --indid from sp_lock
Hope this helps.
Dan Guzman
SQL Server MVP
"McGy" <anon@.anon.com> wrote in message
news:eoPC9chiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Hi. We are experiencing significant prolonged blocking when loading
> multiple files in through a partitioned view. Before we start working on a
> resolution we want to identify exactly what resource is causing the block.
> We can see which processes are being blocked by using sp_who2. But how do
> we determine exactly which lock on which object is causing the blocking?
> Thanks!
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
>|||Thank you very much for all of your tips. They have been invaluable!!
McGy
[url]http://mcgy.blogspot.com[/url]
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OUyKp8iiGHA.4056@.TK2MSFTNGP02.phx.gbl...
> 1) Execute sp_lock for the blocked spid. For example:
> EXEC sp_lock 51
> 2) Identify the row with status WAIT and note the ObjId and indid
> 3) Determine the object/index
> USE MyDatabase
> SELECT
> OBJECT_NAME(id) AS TableName,
> name AS IndexName
> FROM sysindexes
> WHERE
> id = 10000 --ObjId from sp_lock
> AND indid = 1 --indid from sp_lock
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "McGy" <anon@.anon.com> wrote in message
> news:eoPC9chiGHA.4512@.TK2MSFTNGP02.phx.gbl...
>