SQL SERVER – Locking and Blocking – Important Aspect of Database and Effect on Performance – Quiz – Puzzle – 5 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.

Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.

SQL Quiz

Have you ever faced a situation when you are trying to retrieve any data and the query keeps on waiting on the data and eventually times out? One of the reasons for this can be locking. What are the different types of locks inside SQL Server 2008 R2?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Locking and Blocking are non-negotiable when it comes to working with Databases. This support article gives great related information.

Notes of Pinal Dave

Locking and Blocking is an interesting concept. I often see people getting confused between them. Deadlock and Livelock are other concepts which keep on getting people confused as well. I think in today’s note, I have not added any more hint but just asked further questions which are add to the confusion. Well, let me make things more interesting AND confusing. I think it will be a good idea to learn when a query times out due to deadlock and due to server time out or when the server is suffering from performance issues.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#41-43
Simple Example of Snapshot Isolation – Reduce the Blocking Transactions
TRANSACTION, DML and Schema Locks
Introduction to Live Lock – What is Live Lock?
Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction


There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.

Link to participate in SQL Quiz

Note: The SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.

Reference: Pinal Dave (http://blog.sqlauthority.com)

2 thoughts on “SQL SERVER – Locking and Blocking – Important Aspect of Database and Effect on Performance – Quiz – Puzzle – 5 of 31

  1. Dave you do great work!

    I request you to put up lot of documentation and analysis on SP_who2 command explanation and how to release locks etc stuff on your site. I was disappointed by even MSDN, BOL.. people escape explanation by referring BOL. Vo bolta nahi :))

    I searched in your site, not much detailed, please do put up sessions of it..

    with great respect


  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s