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 :)
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.
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.
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.
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.
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 :)