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);

BEGIN TRAN
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;

sp_lock

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
GO

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

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;

KILL <processid> WITH STATUSONLY

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

About these ads

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

unfoldthecreativity

Traveller Observer

The SQL Pro

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

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We will improve your knowledge

SQL Studies

 Live, Learn, Share

Behind-the-Scenes of Xeno Hemlock (dot) com

#TeamAPE (author, publisher, entrepreneur)

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!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Terli Srinivasa rao Blog

Sql server and Tableau practices

Follow

Get every new post delivered to your Inbox.

Join 48 other followers

%d bloggers like this: