Post reply

Compilation lock and blocking

  • March 31, 2017 at 8:14 am

    #322591

    Hello,

    We are seeing compilation lock blocking on one of our production server. Initially we saw it was because of the underlying procedure that was created with sp_. I saw in some of the blogs recommending to not use user stored procedures with sp_....so we renamed it with usp_ but still the compilation lock is occuring on the same stored procedure usp_ (the one we renamed).... after the initial issue, we created a proc with usp_ but left the original sp_ procedure as is. I'm not sure what's causing the blocking. 

    Can anyone shed some light on this ?

    Thanks

  • March 31, 2017 at 8:21 am

    #1935988

    What do you mean by compliation lock - that's not something I've heard of?  Have you tried using sp_whoisactive to trace the blocking chain to its head?

    John

  • March 31, 2017 at 11:05 am

    #1936027

    Hi John,

    i mean a general blocking but in the wait reosurce it like Object : 10:14534532:0 [Compile]...there was a lead blocker session 201 and it's blocking a session 320 that was waiting on this objectid with compile lock and the session 320 is blocking 65 other sessions.waiting on same wait reosurce with Compile.

    Session 201 is running a stored procedure (a report), and the stored proc name starts with sp_ and it calls different procs inline and one of the inline proc starts with sp_ as well, we changed it to usp_ but still we are seeing compile lock on usp_ procedure.....all the 65 sessions including session 320 were waiting on this object with compile lock. Do you know any reason why compile lock occurs, we are using full object name with schema.objectname ....one thing i'm thinking is update stats (that we setup for VLT's ) are kicked at same time which might have caused the recompiles but not entirely sure.

    Any help would be appreciated.

  • April 1, 2017 at 1:37 pm

    #1936123

    You can capture the reasons for recompilation using extended events or profiler.
    With extended events, you would want the event sqlserver.sql_statement_recompile.
    With profiler, SP:Recompile event gives the reason for the recompilation with an integer in the EventSubClass column. The value of the integers is listed here;
    SP:Recompile Event Class

    Sue

  • April 1, 2017 at 5:37 pm

    #1936136

    Sue_H - Saturday, April 1, 2017 1:37 PM

    You can capture the reasons for recompilation using extended events or profiler.
    With extended events, you would want the event sqlserver.sql_statement_recompile.
    With profiler, SP:Recompile event gives the reason for the recompilation with an integer in the EventSubClass column. The value of the integers is listed here;
    SP:Recompile Event Class

    Sue

    Thanks Sue. I will have to setup an Extended session event. Just to make sure, which one is less resource intensive ? XE or Profiler ? I know XE has more granular data but going forward i'm planning to use XE.

    Thanks

  • April 2, 2017 at 8:20 am

    #1936174

    Robin35 - Saturday, April 1, 2017 5:37 PM

    Sue_H - Saturday, April 1, 2017 1:37 PM

    You can capture the reasons for recompilation using extended events or profiler.
    With extended events, you would want the event sqlserver.sql_statement_recompile.
    With profiler, SP:Recompile event gives the reason for the recompilation with an integer in the EventSubClass column. The value of the integers is listed here;
    SP:Recompile Event Class

    Sue

    Thanks Sue. I will have to setup an Extended session event. Just to make sure, which one is less resource intensive ? XE or Profiler ? I know XE has more granular data but going forward i'm planning to use XE.

    Thanks

    Extended events. It's a bit more forward on this event as it will give you the description for the recompile so you don't need to translate the integer values to the meaning/description.

    Sue

  • April 2, 2017 at 12:24 pm

    #1936181

    Sue_H - Sunday, April 2, 2017 8:20 AM

    Robin35 - Saturday, April 1, 2017 5:37 PM

    Sue_H - Saturday, April 1, 2017 1:37 PM

    You can capture the reasons for recompilation using extended events or profiler.
    With extended events, you would want the event sqlserver.sql_statement_recompile.
    With profiler, SP:Recompile event gives the reason for the recompilation with an integer in the EventSubClass column. The value of the integers is listed here;
    SP:Recompile Event Class

    Sue

    Thanks Sue. I will have to setup an Extended session event. Just to make sure, which one is less resource intensive ? XE or Profiler ? I know XE has more granular data but going forward i'm planning to use XE.

    Thanks

    Extended events. It's a bit more forward on this event as it will give you the description for the recompile so you don't need to translate the integer values to the meaning/description.

    Sue

    Thank you.

  • April 3, 2017 at 3:42 am

    #1936225

    The naming is unlikely to be causing the lock. The sp_ prefix means that SQL looks in master and the resource DB before the user DB for the object, that's all.

    Compile locks are taken when the optimiser is generating a plan, to stop the procedure from changing while it's doing so. It shouldn't be causing blocking however, unless you've got stuff trying to change the procedure frequently, or have the plan getting recompiled all the time.
    Do any queries in the procedure have the RECOMPILE hint? Does the procedure have the recompile setting?  If not, can you investigate (via Extended Events and the recompile event) why the procedure's plan is getting recompiled all the time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 3, 2017 at 11:56 am

    #1936374

    Thanks Sue and Gail for the response.

    As i mentioned i have setup and ran the XE session, i found the object is it holding compile lock is because of "Temp table changed" reason. the culprit procedure P2 is being called from the master stored procedure P1. Stored procedure P1 is created a temp table #T1 and doing bunch of stuff and calling stored procedure P2 which is doing insert into same temp table #T1 (we are not creating the temp table again here, just using the #T1 from master procedure)...i guess we can do this but temp table #T1 structure has difference (difference in number of columns) between procs P1 and P2 which i think is causing the recompilation. Please confirm if this is true.

    Also, there are different stored procs that hold compilation locks on procedure P2, out of all only one stored procedure has Option (recompile) hint which is sometimes causing compilation locks.
    I also see some times stored procedures blocking the auto update stats (generated by system) running under the context of master database.

  • April 3, 2017 at 2:41 pm

    #1936422

    Yup, a design like that will cause recompilations on every execution, and if it's executed frequently will cause compile locks (only one session can be compiling a query at a time). Can you rework the procedures so that P2 does not use a temp table that it hasn't created?

    Is the recompile hint needed?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 3, 2017 at 3:00 pm

    #1936423

    GilaMonster - Monday, April 3, 2017 2:41 PM

    Yup, a design like that will cause recompilations on every execution, and if it's executed frequently will cause compile locks (only one session can be compiling a query at a time). Can you rework the procedures so that P2 does not use a temp table that it hasn't created?

    Is the recompile hint needed?

    Thanks Gail. Yes, you are right, it creating new plan for every execution because of recompilation. We are planning to include all the columns of #T1 in P2 to make sure it has same number of columns as P1, it's the same temp table but in P2 we are inserting data only into 5 columns out of 10 columns but still not sure why would optimizer decides it's a temp table change  ? Recompile hint is on a different stored procedure, it is actually not required but this proc generates a dynamic sql which always causes recompilation for some reason because the way user passes the arguments and hence we decided to keep option recompile at a particular statement that always causes issue with new CE in 2014, we are also using the legacy CE with querytraceon 9481 for this procedure. Basically we followed below blog.

    https://www.sqlskills.com/blogs/kimberly/sp_settraceflag/

  • April 4, 2017 at 3:24 am

    #1936468

    Robin35 - Monday, April 3, 2017 3:00 PM

    GilaMonster - Monday, April 3, 2017 2:41 PM

    Yup, a design like that will cause recompilations on every execution, and if it's executed frequently will cause compile locks (only one session can be compiling a query at a time). Can you rework the procedures so that P2 does not use a temp table that it hasn't created?

    Is the recompile hint needed?

    Thanks Gail. Yes, you are right, it creating new plan for every execution because of recompilation. We are planning to include all the columns of #T1 in P2 to make sure it has same number of columns as P1, it's the same temp table but in P2 we are inserting data only into 5 columns out of 10 columns but still not sure why would optimizer decides it's a temp table change  ?

    Because you didn't create it in that procedure (at least that's how it used to behave last time I ran into this problem)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 4, 2017 at 3:31 pm

    #1936657

    GilaMonster - Tuesday, April 4, 2017 3:24 AM

    Robin35 - Monday, April 3, 2017 3:00 PM

    GilaMonster - Monday, April 3, 2017 2:41 PM

    Yup, a design like that will cause recompilations on every execution, and if it's executed frequently will cause compile locks (only one session can be compiling a query at a time). Can you rework the procedures so that P2 does not use a temp table that it hasn't created?

    Is the recompile hint needed?

    Thanks Gail. Yes, you are right, it creating new plan for every execution because of recompilation. We are planning to include all the columns of #T1 in P2 to make sure it has same number of columns as P1, it's the same temp table but in P2 we are inserting data only into 5 columns out of 10 columns but still not sure why would optimizer decides it's a temp table change  ?

    Because you didn't create it in that procedure (at least that's how it used to behave last time I ran into this problem)

    Ok thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply