DBCC INDEXDEFRAG aquires exclusive locks on a table

Hello!
I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular basis. I
have noticed that when indexing one of the non-clustered indexes on
particular table, process acquired exclusive(X) lock on the entire table
thus blocking any SELECTs etc.
When I was running my sample tests, I was observing IX lock on table in
question. I am not sure why exclusive lock was acquired by SQL Server job.
I was wondering if anybody experienced similar behavior.
Thanks,
Igorimarchenko wrote:
> Hello!
> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
> basis. I have noticed that when indexing one of the non-clustered
> indexes on particular table, process acquired exclusive(X) lock on
> the entire table thus blocking any SELECTs etc.
> When I was running my sample tests, I was observing IX lock on
> table in question. I am not sure why exclusive lock was acquired by
> SQL Server job. I was wondering if anybody experienced similar
> behavior.
> Thanks,
> Igor
IX is just an Intent lock. Form BOL: "Indicates the intention of a
transaction to modify some (but not all) resources lower in the
hierarchy by placing X locks on those individual resources. IX is a
superset of IS." Indexing places exclusive locks on a table, whereas
indexdefrag does not. Am I understanding your question correctly?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Sorry if I didn't make myself clear.
The problem is that IX lock is eventually being escalated into X lock. I
was under impression that DBCC INDEXDEFRAG issues series of short
transactions and never places X lock on entire table. Any thoughts are
greatly appreciated.
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23OdFlDc1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> imarchenko wrote:
>> Hello!
>> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
>> basis. I have noticed that when indexing one of the non-clustered
>> indexes on particular table, process acquired exclusive(X) lock on
>> the entire table thus blocking any SELECTs etc.
>> When I was running my sample tests, I was observing IX lock on
>> table in question. I am not sure why exclusive lock was acquired by
>> SQL Server job. I was wondering if anybody experienced similar
>> behavior.
>> Thanks,
>> Igor
> IX is just an Intent lock. Form BOL: "Indicates the intention of a
> transaction to modify some (but not all) resources lower in the hierarchy
> by placing X locks on those individual resources. IX is a superset of IS."
> Indexing places exclusive locks on a table, whereas indexdefrag does not.
> Am I understanding your question correctly?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||imarchenko wrote:
> David,
> Sorry if I didn't make myself clear.
> The problem is that IX lock is eventually being escalated into X
> lock. I was under impression that DBCC INDEXDEFRAG issues series of
> short transactions and never places X lock on entire table. Any
> thoughts are greatly appreciated.
My understanding is that DBCC INDEXDEFRAG is an online operation whereas
CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
short transactions to make its changes, those changes could require
varying levels of locks on the underlying table.
Maybe someone else can offer additional information on lock escalation
with the command.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
modification) lock that is incompatible with any other locks whereas DBCC
INDEXDEFRAG starts with X lock on row/page level (IX on table level) that is
being escalated into X table lock under certain circumstances (I suspect it
is table size/defragmantation level related).
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
> short transactions to make its changes, those changes could require
> varying levels of locks on the underlying table.
> Maybe someone else can offer additional information on lock escalation
> with the command.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there should
only be IX lock at the table level. I'll ask around and will post back if I get any reply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that is
> incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page level
> (IX on table level) that is being escalated into X table lock under certain circumstances (I
> suspect it is table size/defragmantation level related).
> Igor
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is an
>> offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>|||Thanks a lor Tibor for looking into this. Please let me know if you will
need more details. Computer is running SQL Server 2000 SP4 on Windows 2003
EE.
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>level. AFAIK, there should only be IX lock at the table level. I'll ask
>around and will post back if I get any reply.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas DBCC
>> INDEXDEFRAG starts with X lock on row/page level (IX on table level) that
>> is being escalated into X table lock under certain circumstances (I
>> suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
>> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
>> short transactions to make its changes, those changes could require
>> varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||I got it confirmed that the escalation does occur, which is a bug. Here's a quote from my contact at
MS:
"Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL locks
and eventually escalate to a table lock. The KB article number is 907250 but
it hasn't been released yet. There is a hotfix available already."
So, I'd contact PSS on this, so you can get the hotfix if you find you need it. Or wait for the KB
to be released to you can read more details about it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will need more details.
> Computer is running SQL Server 2000 SP4 on Windows 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there
>>should only be IX lock at the table level. I'll ask around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that
>> is incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page
>> level (IX on table level) that is being escalated into X table lock under certain circumstances
>> (I suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is
>> an offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
locks on pages its moved - this eventually causes the next requested X page
lock to escalate to an X table lock.
A hotfix is available through PSS - it's not made it to the web yet. There
will also be a KB article but it hasn't made it out yet either. You should
be able to reference case SRX050805601805 with PSS and the fix will be
provided free of charge.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will
> need more details. Computer is running SQL Server 2000 SP4 on Windows
> 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG
>> using short transactions to make its changes, those changes could
>> require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||Tibor,
Thanks a lot for finding the answer so promptly!
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CXN4vo1FHA.2880@.TK2MSFTNGP12.phx.gbl...
>I got it confirmed that the escalation does occur, which is a bug. Here's a
>quote from my contact at MS:
> "Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL
> locks
> and eventually escalate to a table lock. The KB article number is 907250
> but
> it hasn't been released yet. There is a hotfix available already."
> So, I'd contact PSS on this, so you can get the hotfix if you find you
> need it. Or wait for the KB to be released to you can read more details
> about it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Thanks a lot!
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Could you please explain what NL is?
Thanks,
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>