Thursday, March 8, 2012

blocking in sysindexes

When a user ran this query:
set nocount on
select top 100 x1.a5plcr, x2.aiplcr
from PRODDTA.F0301_vw x1, PRODDTA.F03012_vw x2
where (x1.a5an8 = x2.aian8)
and x1.a5plcr <> x2.aiplcr --order by x1.a5an8 option (maxdop 1)
We showed both Exclusive and Intent-X locks on sysindexes of the user
database.
The two views in the FROM are both single-table views with NOLOCK.
Why was sysindexes exclusively locked in this scenario? Why was it touched
at all?
Thanks,
Kevin3NFSysindexes is always accessed when a query is being compiled since that is
where the statistics live. But I don't know why it had an exclusive lock.
It is possible that while the plan is being compiled (or recompiled) it will
lock that particular row in sysindexes so it does not get changed during the
compile but that is just a theory at the moment.
Andrew J. Kelly SQL MVP
"Kevin3NF" <Kevin3NF@.discussions.microsoft.com> wrote in message
news:AF6AA251-D689-4FB7-80B6-253CB6B0D07D@.microsoft.com...
> When a user ran this query:
> set nocount on
> select top 100 x1.a5plcr, x2.aiplcr
> from PRODDTA.F0301_vw x1, PRODDTA.F03012_vw x2
> where (x1.a5an8 = x2.aian8)
> and x1.a5plcr <> x2.aiplcr --order by x1.a5an8 option (maxdop 1)
> We showed both Exclusive and Intent-X locks on sysindexes of the user
> database.
> The two views in the FROM are both single-table views with NOLOCK.
> Why was sysindexes exclusively locked in this scenario? Why was it
> touched
> at all?
> Thanks,
> Kevin3NF|||Thanks Andrew...must have been a recompile going on, which is possible given
the OLTP nature of the DB in question and the fact that Auto Update Stats is
turned on.
Kevin3NF
www.3nf-inc.com
"Andrew J. Kelly" wrote:

> Sysindexes is always accessed when a query is being compiled since that is
> where the statistics live. But I don't know why it had an exclusive lock
.
> It is possible that while the plan is being compiled (or recompiled) it wi
ll
> lock that particular row in sysindexes so it does not get changed during t
he
> compile but that is just a theory at the moment.
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin3NF" <Kevin3NF@.discussions.microsoft.com> wrote in message
> news:AF6AA251-D689-4FB7-80B6-253CB6B0D07D@.microsoft.com...
>
>

No comments:

Post a Comment