On 12th of June I will be presenting for the PASS Virtual Chapter, so join me and learn more about SQL Sever locking, blocking and isolation levels! We will discuss how the locking manager actually works, what are the various problems caused by the process of locking, how can we actually troubleshoot them and where do isolation levels actually fit in this picture.
You can register for the webinar here and check what time it will be in your timezone here!
Hope to see you all online!
Update: I had so much fun during this presentation! Thank you all for joining me and thanks to the organisers of PASS Virtual Chapter for inviting me! Below you can check the slide deck from my session and the questions that I did not have enough time to answer to.
Q: I want historical blocking info – like happenned a day or 2 ago? [Suresh Raavi]
A: I am not aware of any built-in way for you to do that. You will have to set it up and capture the locking/blocking info somewhere. For example – set the Blocking Processes Report, capture the event and send the output to a table or file. You can capture it with both Profiler and Extended Events. You know which one you have to choose! Another option is to capture the output of sp_whoIsActive again in a table or file. It’s totally up to you.
Q: How to prevent deadlock on cascading delete for 2 different delete statements executed via different proc trying to use same table?[H G]
A: Can you point me to a code that I can take a look at. Until then though, have you tried to run the 2 delete statements serially?
Q: What is disadvantage of “AUTO” table escalation lock? [Mehmet Güzel]
Q: Why is not default table escalation lock “auto”? [Mehmet Güzel]
A: Great question and it’s answer is in Books Online. To sum it up for you – there are chances for deadlocks and that is why TABLE is the default lock escalation level.
Q: For row versioning will all data read result in a physical write to disk for the tempdb or can it do this all in memory? [James King]
A: It all goes to tempdb. From there you read it and it goes then to memory, so think again about what will “happen” to your tempdb database…
Q: Can you please the book isbns or name of the books? [Manjunath Jayanna]
A: Microsoft SQL Server Internals (ISBN-10: 0735626243, ISBN-13: 978-0735626249), SQL Server 2008 Query Performance Distilled (ISBN-10: 1430219025, ISBN-13: 978-1430219026)
Q: What’s your hangout handle? [Jeromy Kimani]
A: You can reach me here -> https://plus.google.com/116047053532709671184/ I am not sure how I have to create a handle for the hangout, but will be happy if you can share it with me and why not do one together on a topic that we choose? Ping me and we will arrange it!
Q: Was this recorded? [John Danley]
A: Yes, it was and it will be available in the PASS Virtual Chapter web site! Stay tuned!