Post reply

Why blocking issue resolves by UPDATE STATISTICS?

  • July 16, 2012 at 5:27 am

    #259472

    Hi,

    I see one SPID hanging for up to 20 minutes with status "Runnable" and blocking others that wait for executing same application flow.

    The lead blocker SPID has an open transaction and has already taken U KEY-lock in one table (T1) - that is what others wait for to be released.

    Now lead blocker issues a SELECT statement on second table (T2) and gets into status "Runnable" for minutes.

    According to sys.dm_tran_locks there are only U KEY-locks on T1 chained up on the first blocked SPID on the server.

    Definition of status Runnable (http://support.microsoft.com/kb/224453) : Runnable - The SPID is in the runnable queue of a scheduler and waiting to get scheduler time.

    I cannot reproduce this behaviour in my test environment even if I fire hundreds of parallel application threads with this flow from several application servers.

    The funny part is that blocking chain gets resolved after issuing "UPDATE STATISTICS T2".

    Some things I cannot explain/understand and need your expertise:

    1) Why/how UPDATE STATISTICS resolves the blocking?

    2) What can cause such a long wait to get scheduler time and what is the best way to deal with situations as such?

    My best guess on the "1)" is assuming that at statistics update the compiled plan for SELECT on T2 gets invalidated and gets out of procedure cache (before it actually started) causing the request to be rescheduled and this time it gets scheduler time with no problem.

    Please share your ideas...

  • July 16, 2012 at 6:38 am

    #1513027

    Sounds about right. The stat update will cause a recompile, and either the recompile itself, or a new execution plan it arrives at from the updated stats doesn't result in the same level of lock escalation, or the updated stats (new plan) cause an increase in speed that makes it hold the locks for less time.

    Either way, it makes sense.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 16, 2012 at 8:40 am

    #1513111

    Thanks GSquared. Now my question is why a process gets stuck in status runnable for a long time? It does not wait for any locks to be released.

  • July 16, 2012 at 8:52 am

    #1513129

    "Runnable" isn't about locks. It's about resource-availability. CPU, et al. It's waiting for a scheduler to be available to pick it up and actually get it running. Of course, once a CPU/thread picks it up, it will stay "runnable" till it finishes, if I understand the internals correctly on that point. So, it's not about blocking/locking, it's about available CPU resources.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 16, 2012 at 9:23 am

    #1513150

    Thanks for clarification, I got it.

    This SPID get stuck forever unless i rebuild statistics and this is hard to explain if there is no CPU contention.

    I need to see what exactly prevents this task to be executed. I guess there are ways through system dm views to see the current state of schedulers, executing tasks and tasks waiting in queues. Maybe this task is waiting for a huge memory grant or there are too many tasks in queues - how can I see that? I do not know what dmv to query in order to see this picture.

    Can you give me a hint?

  • July 16, 2012 at 9:39 am

    #1513160

    Start with an online search for "sql server wait states". You'll find a ton of information on the subject and can research it from there. It gets a little thick at times, but it's important to understand it if you're debugging slow servers or queries.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 17, 2012 at 10:53 am

    #1513722

    get sp_whoisactive from SQLBlog.com (v11.11 is latest last I heard) and use that to get all KINDS of important information about executions on your sql server

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 19, 2012 at 6:50 am

    #1514809

    GSquared (7/16/2012)

    "Runnable" isn't about locks. It's about resource-availability. CPU, et al. It's waiting for a scheduler to be available to pick it up and actually get it running. Of course, once a CPU/thread picks it up, it will stay "runnable" till it finishes, if I understand the internals correctly on that point. So, it's not about blocking/locking, it's about available CPU resources.

    Thanks, I totally get that. I will collect information about resource usage (CPU, et al.) at the time of blocking next time. I even prepared script to get state of workers and schedulers. One thing I understand differently from what you've said - "once a CPU/thread picks it up, it will stay "runnable" till it finishes" - as I understand the SPID gets status "Running" when it actually executing the task. Please correct me if I'm wrong.

  • July 19, 2012 at 6:53 am

    #1514811

    seregak76 (7/19/2012)

    GSquared (7/16/2012)

    "Runnable" isn't about locks. It's about resource-availability. CPU, et al. It's waiting for a scheduler to be available to pick it up and actually get it running. Of course, once a CPU/thread picks it up, it will stay "runnable" till it finishes, if I understand the internals correctly on that point. So, it's not about blocking/locking, it's about available CPU resources.

    Thanks, I totally get that. I will collect information about resource usage (CPU, et al.) at the time of blocking next time. I even prepared script to get state of workers and schedulers. One thing I understand differently from what you've said - "once a CPU/thread picks it up, it will stay "runnable" till it finishes" - as I understand the SPID gets status "Running" when it actually executing the task. Please correct me if I'm wrong.

    Should do so, but I've seen one stay "runnable" right up till the moment it actually finished. Was probably "running" for some small fraction of a second after being "runnable" for several hours, on a very complex, multi-million-row operation, which definitely took a long time to actually run.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 19, 2012 at 7:26 am

    #1514836

    TheSQLGuru (7/17/2012)

    get sp_whoisactive from SQLBlog.com (v11.11 is latest last I heard) and use that to get all KINDS of important information about executions on your sql server

    Thanks, I looked at it and will certainly get into my toolbox 🙂

  • July 19, 2012 at 7:46 am

    #1514847

    GSquared (7/19/2012)

    seregak76 (7/19/2012)

    GSquared (7/16/2012)

    "Runnable" isn't about locks. It's about resource-availability. CPU, et al. It's waiting for a scheduler to be available to pick it up and actually get it running. Of course, once a CPU/thread picks it up, it will stay "runnable" till it finishes, if I understand the internals correctly on that point. So, it's not about blocking/locking, it's about available CPU resources.

    Thanks, I totally get that. I will collect information about resource usage (CPU, et al.) at the time of blocking next time. I even prepared script to get state of workers and schedulers. One thing I understand differently from what you've said - "once a CPU/thread picks it up, it will stay "runnable" till it finishes" - as I understand the SPID gets status "Running" when it actually executing the task. Please correct me if I'm wrong.

    Should do so, but I've seen one stay "runnable" right up till the moment it actually finished. Was probably "running" for some small fraction of a second after being "runnable" for several hours, on a very complex, multi-million-row operation, which definitely took a long time to actually run.

    Intresting, is it a bug? Or can it be that this "very complex, multi-million-row operation" run in parallel and the head thread stayed "Runnable" while child threads were actually running tasks? Please do not get me wrong, I only trying to learn from you.

    My lead blocker SPID tries to run trivial 5 reads CIX seek. I is impossible to see it in running state since it should run in few mkseconds. So right now I try to learn more about spinlock contention since this is the only lead I have left.

  • July 23, 2012 at 7:02 am

    #1516203

    Don't know if it's a bug, or if some piece of it was "runnable", while other pieces were "running", and it showed as "runnable" because of that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 23, 2012 at 8:38 am

    #1516276

    Runnable is fine...but your process is only stuck if it is blocked by some other SPID. If this is not the case then your query is working. Check the active sessions (using SP_WHO2 'active') and see if there is no SPID listed in "blkby" column against your SPID.

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

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

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