70-457 Reviewer #07, Resolving Concurrency Problems in SQL Server 2012

This topic is part of the lessons covered in Optimization and Troubleshooting SQL Server 2012 which is 14% of overall scoring in the exam.

For this article, I will cover the following subtopics;

  • Resolving Concurrency Problems
  • Blocks and Deadlocks
  • Identifying Issues
  • Resolving Issues

Resolving Concurrency Problems

Many factors can affect the concurrency of a database application. As a DBA we must know how to provide higher levels of concurrency in our database. One way to improve concurrency of your database application is to ensure that transaction scopes are kept as short as possible, allowing SQL Server to release the lock in the shortest possible time. Transactions are one way to ensure the ACID properties of a database. Please visit this link to learn more about ACID (Atomicity, Consistency, Isolation, Durability)

SQL Server will always lock the minimum number of resources needed to accomplish its goal, this is known as multi-granular locking. Because of this scheme, SQL Server must not only take the lock it needs, but it must also announce its intent to high levels via INTENT LOCK. I will try to explain this better with my sample below. This is also a way on how you can identify if locking is happening now in your database.

I created a simple table, inserted a couple of rows and issued the statements below (notice that I do not have a COMMIT or ROLLBACK so making this an incomplete transaction);

UPDATE LockTest SET SomeData = ‘updating here’ WHERE ID = 1

Then run the stored procedure sp_lock to check your processes.  The stored procedure sp_lock will give you results as below;


As you know the spid, is the session process id, dbid  is your database id, objid  is the object id and the type is the lock type and the resource column tells you where the lock is. So if the lock type is a page lock then the resource column should display as 4:8 where 4 is the actual data file and the 8 is the page number. If its a row lock then the display should 4:8:3 where the last number is the row id. The mode column tells you what type of lock, so for row number 4 on the above result, the mode column tells me its an exclusive lock on a row 4:8:3. SQL Server will then do an intent lock above that, on a page 4:8 (see row number 3) and another intent lock on that table (see row number 5). So that’s how the hierarchy of intent locks work in SQL Server.

Another helpful stored procedure we can use is the sp_who2. From the results in the sp_who2 check out the column STATUS. Any value of SUSPENDED in this column tells you that it is being blocked by some process. You can see who is blocking this in the BLKBY column.

There are a couple of DMVs you can check also that gives similar results when you run sp_who2.

–DMV to view locking information
SELECT * FROM sys.dm_tran_locks

–DMV to view blocked transactions
SELECT * FROM sys.dm_exec_requests WHERE status = ‘suspended’

If you are currently experience a locking in your server, you can also run the Activity Monitor in your SSMS. Right click on the server in the object explorer and select Activity Monitor from the menu. This will launch the Activity Monitor screen as below. This is also a cool tool because you can actually right click on the process which is causing the lock, right click and kill process 🙂

Activity Monitor

One best practice, to reduce concurrency, that you can always put into practice is to have clustered index created in your tables. Queries against heap tables can lead into table locks because SQL Server will search and scan in all the leaf pages for results that match the query predicate.

Blocks and Deadlocks

A deadlock occurs when there are 2 or more tasks permanently blocking each other because each task has a lock on a resource that the other task wants to lock. Each user session might have one or more task running, and each task might acquire or wait to acquire a variety of resources.

SQL Server has a thread dedicate solely to be on the lookout for these deadlocks. By default this thread refreshes every 5 seconds. If it encounters a deadlock, the deadlock monitor increases this checking more frequently until it finds no more deadlocks and it resets back to checking every 5 seconds.

Deadlocks raise a 1205 error. When a deadlock is encountered, one of the process selected by the deadlock monitor to be terminated and rolled back. The selected process is called the deadlock victim. The deadlock victim is usually the process that has generated the fewest transaction log records. You can override this behavior by setting the DEADLOCK_PRIORITY on the connection involved.

Identifying Issues

It is best to be proactive on checking out these locks. As a DBA it is your primary role to be ahead of this locks. One thing you can do is to create email alerts once the total number of locks in your server is greater than 0. You can set this up in your SQL Agent and go to Alerts to create new alerts. You can use the screenshot below as guideline which parameters to use.

Deadlock Alert

In SQL Server 2012, the SQL Trace has been deprecated. Instead you can use the feature called Extended Events which has a lower overhead method of capturing deadlock information. One of the session in Extended Events that is always running is the system_health session. This session has the xml_deadlock_report event that you can view in SSMS. From the object explorer, expand the Management > Extended Events > Sessions > system_health right click on this one to go to Properties and this will open the dialog box as below.

Extended Events

From here, go to Events on the side menu, from the selected events select xml_deadlock_report and click Configure. From there you can select which data to collect for your deadlock report.

One more reporting that can help you in your system monitoring for deadlocks is the SQL Server 2012 Dashboard Reports. This is a separate tool that you need to install. You can download it from here. After you download and run the installation tool, you can access it in the object explorer, right click on the instance, select Reports, and select Custom Reports. Make sure to remember where the dashboard files are in your system, in my case I installed it in C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard. It is great to get familiar to this tool because it presents so much information on just about everything in your SQL Server.

Resolving Issues

One way to resolve deadlocks is to use the KILL command. That’s why its very important to remember the process id because you will need it in using KILL command.

When SQL Server kills a process it must remain transactionally consistent and might have to rollback its transaction. This can be somewhat lengthy in process, so we need to add WITH STATUS ONLY option to give periodic reports to the console indicating how much work has to be rolled back;


Ahm, one more thing, you cannot use this KILL command to kill your own process. That is suicide 🙂


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: