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...
>

Disable Index on a Specific Partition

I'm performing incremental loads into a 13 mths partitioned table with
indexes alinged by partition. I'm trying to figure out how to disable the
indexes on a specific partition. I've researched and can't seem to fine an
option with the Alter Index Statement that would drop or disable the indexes
on just one partition(current mnth) and than rebuild the indexes for just
this one partition. Well there is a Rebuild option per partition but there
isn't one that I've come across thus far for Disable.I don't think it's possible to selectively disable or drop indexes on a
specific partition. You might instead consider switching the partition to a
separate maintenance table, drop those indexes, load, rebuild indexes and
then switch the table back in. If the target partition is empty, you don't
need to bother switching out.
Hope this helps.
Dan Guzman
SQL Server MVP
"Socrates" <Socrates@.discussions.microsoft.com> wrote in message
news:7D46AD52-ED4D-41C2-93DA-23E58CCF5A85@.microsoft.com...
> I'm performing incremental loads into a 13 mths partitioned table with
> indexes alinged by partition. I'm trying to figure out how to disable the
> indexes on a specific partition. I've researched and can't seem to fine an
> option with the Alter Index Statement that would drop or disable the
> indexes
> on just one partition(current mnth) and than rebuild the indexes for just
> this one partition. Well there is a Rebuild option per partition but there
> isn't one that I've come across thus far for Disable.

Disable Index on a Specific Partition

I'm performing incremental loads into a 13 mths partitioned table with
indexes alinged by partition. I'm trying to figure out how to disable the
indexes on a specific partition. I've researched and can't seem to fine an
option with the Alter Index Statement that would drop or disable the indexes
on just one partition(current mnth) and than rebuild the indexes for just
this one partition. Well there is a Rebuild option per partition but there
isn't one that I've come across thus far for Disable.I don't think it's possible to selectively disable or drop indexes on a
specific partition. You might instead consider switching the partition to a
separate maintenance table, drop those indexes, load, rebuild indexes and
then switch the table back in. If the target partition is empty, you don't
need to bother switching out.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Socrates" <Socrates@.discussions.microsoft.com> wrote in message
news:7D46AD52-ED4D-41C2-93DA-23E58CCF5A85@.microsoft.com...
> I'm performing incremental loads into a 13 mths partitioned table with
> indexes alinged by partition. I'm trying to figure out how to disable the
> indexes on a specific partition. I've researched and can't seem to fine an
> option with the Alter Index Statement that would drop or disable the
> indexes
> on just one partition(current mnth) and than rebuild the indexes for just
> this one partition. Well there is a Rebuild option per partition but there
> isn't one that I've come across thus far for Disable.

Disable Index on a Specific Partition

I'm performing incremental loads into a 13 mths partitioned table with
indexes alinged by partition. I'm trying to figure out how to disable the
indexes on a specific partition. I've researched and can't seem to fine an
option with the Alter Index Statement that would drop or disable the indexes
on just one partition(current mnth) and than rebuild the indexes for just
this one partition. Well there is a Rebuild option per partition but there
isn't one that I've come across thus far for Disable.
I don't think it's possible to selectively disable or drop indexes on a
specific partition. You might instead consider switching the partition to a
separate maintenance table, drop those indexes, load, rebuild indexes and
then switch the table back in. If the target partition is empty, you don't
need to bother switching out.
Hope this helps.
Dan Guzman
SQL Server MVP
"Socrates" <Socrates@.discussions.microsoft.com> wrote in message
news:7D46AD52-ED4D-41C2-93DA-23E58CCF5A85@.microsoft.com...
> I'm performing incremental loads into a 13 mths partitioned table with
> indexes alinged by partition. I'm trying to figure out how to disable the
> indexes on a specific partition. I've researched and can't seem to fine an
> option with the Alter Index Statement that would drop or disable the
> indexes
> on just one partition(current mnth) and than rebuild the indexes for just
> this one partition. Well there is a Rebuild option per partition but there
> isn't one that I've come across thus far for Disable.