Compile Blocking Issues

We've recently been experiencing problems locking issues seemingly caused by
compiles.. ..cpu max's out at 100%, query duration get longer, and we see a
large number of LCK_M_X in sysprocesses with coupled with something like TAB:
5:736291420:0 [COMPILE].. ..what could be causing this issue?
K1) What is the table referenced in the lock?
2) It could be caused by lots of compiles' :-)) Seriously, do you do a
lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
temptable useage (it is SCARY how many recompiles can be caused by this!!)?
SQL 2000 or 2005'
TheSQLGuru
President
Indicium Resources, Inc.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Have you read this:
"Description of SQL Server blocking caused by compile locks"
http://support.microsoft.com/kb/263889
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||On Jun 13, 11:49 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) What is the table referenced in the lock?
> 2) It could be caused by lots of compiles' :-)) Seriously, do you do a
> lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
> temptable useage (it is SCARY how many recompiles can be caused by this!!)?
> SQL 2000 or 2005'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Ben UK" <B...@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>
> > We've recently been experiencing problems locking issues seemingly caused
> > by
> > compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> > a
> > large number of LCK_M_X in sysprocesses with coupled with something like
> > TAB:
> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> > K- Hide quoted text -
> - Show quoted text -
I've observed this behavior with sprocs that are called very often and
make use of temp tables. As data is inserted into the temp table one
or more sp-recompile events will happen. If many concurrent spids are
calling this sproc at the same time, SQL appears to allow only one
spid at a time to perform the recompile - this will reduce the level
of concurrency in the system and you will see blocking spids that are
marked with [COMPILE]. Try to use profiler to trace stored procedure
recompiles, and RPC:Completed events to identify what is being
recomplied, then ideally try to tune the code to reduce recompiles.|||Thanks for the responses, we're SQL 2005, SP1, the objects referenced in the
lock are primarily 2 sp's and 1 udf.. ..when querying sysprocesses we can see
around 30 occurances of this lock from around 600 connections.
The problems *seem*to have started since we changed the schema and removed a
table containing denormalized data and replaced it with a view. Both the
sp's that have compile issues reference the new view (as do around 50-60
more), the udf doesn't reference any new tables... ...the udf uses a table
variable, but neither sp uses temp tables of any kind.
http://support.microsoft.com/kb/263889
^ I did read the article earlier today.. ..it was kinda useful, but I didn't
see anything in there that would indicate the cause of our issue. The only
thing it made me question was some of the table with the sp's weren't fully
qualified.. ..but this has always been the case so it would be strange for
this to only just start causing a problem..
Again thanks for the responses.. ..any help is greatly appreciated
K
Unfortunately I can't analyse new traces, as currently our frontend is being
redirected..|||Is 736291420 an object id for a stored proc? It sounds like what MS calls
"rolling block". Does the blocking head spid(s) constantly changing?
Did you run profiler trace to see where the SP:Recompile event occurs and
what Event Subclass it falls into?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Yep it's a sproc and the blocking head does constantly change.. ..what causes
this behaviour?
Thanks in advance
K
"YPD" wrote:
> Is 736291420 an object id for a stored proc? It sounds like what MS calls
> "rolling block". Does the blocking head spid(s) constantly changing?
> Did you run profiler trace to see where the SP:Recompile event occurs and
> what Event Subclass it falls into?
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> >
> > We've recently been experiencing problems locking issues seemingly caused
> > by
> > compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> > a
> > large number of LCK_M_X in sysprocesses with coupled with something like
> > TAB:
> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> >
> > K
>
>|||The culprit for the performance problem should be stored procedure
recompilations. What happens is the sprocs are recompiled everytime they get
called. The recompliation doesn't happen in a timely fasion so that client
connections calling the sprocs have to be queued up waiting to be
recompiled.
Profiler is your friend. Please run a profiler trace to capture a series of
events to determine what caused the recomplications. The link
http://support.microsoft.com/kb/243586/ referenced in Kalen's post is a good
place to start.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:AA4556BF-995D-4201-B887-36C04FEB9E84@.microsoft.com...
> Yep it's a sproc and the blocking head does constantly change.. ..what
> causes
> this behaviour?
> Thanks in advance
> K
> "YPD" wrote:
>> Is 736291420 an object id for a stored proc? It sounds like what MS calls
>> "rolling block". Does the blocking head spid(s) constantly changing?
>> Did you run profiler trace to see where the SP:Recompile event occurs and
>> what Event Subclass it falls into?
>>
>> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>> >
>> > We've recently been experiencing problems locking issues seemingly
>> > caused
>> > by
>> > compiles.. ..cpu max's out at 100%, query duration get longer, and we
>> > see
>> > a
>> > large number of LCK_M_X in sysprocesses with coupled with something
>> > like
>> > TAB:
>> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
>> >
>> > K
>>