SQL Server Quickie #33 – Deadlocks

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Today I have uploaded SQL Server Quickie #33 to YouTube. This time I’m talking about Deadlocks in SQL Server.

Like or share to get the source code.

Thanks for your time,

-Klaus

2 Comments

  • Hello KLaus,

    Thanks for this informative video.

    In terms of deadlocks I’ve got a couple of questions that I haven’t found definitive answers for:

    We seem to have recurring intra-query parallelism deadlocks. To what extent are these deadlocks critical, how does SQL server remedy them and do they also trigger a Msg 1205 and a deadlock graph in profiler? They seem not to do that.

    Another point: If I query historical deadlock info from the DB, I get a resultset of about 120 entries. However, if I query the number of deadlocks that have occured since last server-restart, I get back ‘7’.
    (I use the standard queries of the SQL server community for this)

    It seems that these seven deadlocks are of the type you’ve been talking about and the intra-query deadlocks don’t count.

    I would appreciate your feedback.

    Kind regards,

    Paul

    • Klaus Aschenbrenner

      Hello Paul,

      Thanks for your comment.
      Intra-parallelism deadlocks are bugs in SQL Server, which are not fixed by Microsoft 😉
      In that case you have to make sure that your query runs single-threaded.
      When you get such a deadlock, you still have the deadlock graph available, and yes the transaction is rolled back with error number 1205.

      Thanks,

      -Klaus

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top