SQL Server Reference Guide

After you know the basics about how locking and blocking work in SQL Server, you'll want to monitor them so that you can fix any errors they cause. While locking is a natural part of any Relational Database Engine, excessive locking will slow down your system, especially during data entry. And deadlocking will certainly cause a slowdown for the two connections involved, and potentially others as well.

Microsoft SQL Server provides several tools you can use to locate the locks, blocks and deadlocks on your system and in some cases even provide the users or queries to track them down and deal with them. In this tutorial I'll focus on the monitoring tactics and in another tutorial I'll show you how to use Isolation Levels in your queries to deal with these issues.

There are five main tools we'll examine in this tutorial:

• The Windows System Monitor (formerly PerfMon or Performance Monitor)
• Stored Procedures
• System Views
• SQL Server Profiler
• SQL Trace

You can also use the Database Tuning Advisor for design and code issues, but I'll save that discussion for its own tutorial. We'll open several tools for this tutorial, so make sure you're on a test system.

Let's start by creating a test database and tables to work with. Since I'm not sure which samples you might have, we'll make a simple database and a table to demonstrate the locking, blocking and deadlocking. Once you open Query Analyzer or SQL Server Management studio, open three query windows. I'll indicate which commands to run on which query window.

Once you have your query windows open, run this command to create the database and test table:

CREATE DATABASE LockTest;
GO
USE LockTest;
GO
CREATE TABLE TestTableA (Collumn1 int)
CREATE TABLE TestTableB (Collumn1 int)
INSERT TestTableA 
VALUES (1);
GO
INSERT TestTableB 
VALUES (2);
GO

With that database and table created, we can move forward on the testing to show locks and blocks.

The Windows System Monitor (formerly PerfMon or Performance Monitor)

We'll start out with the Windows System Monitor. This is a new tool for Windows, and it replaced the older Performance Monitor or PerfMon. You'll often hear the Windows System Monitor referred to as Performance Monitor, but technically they are different. You can still download and run the old PerfMon tool, but it doesn't do nearly as much as the newer System Monitor.

The reason why I start with this tool is that this is what I often do on the job, since it's one of the least intrusive tools I can use. I've explained how the tool works before, but the basics are that there are three parts to the process. Each product that is "instrumented" to work with the System Monitor has corresponding registry entries. The product writes to the registry location each timing interval, and the Windows System Monitor reads the entries just after.

Although the Windows System Monitor doesn't have a high impact on the system, if you run it remotely or from several machines at once it can bog down the network or system as a whole. As I've described before, I run the process from time to time and collect the data into a log file to read later. I also run this tool when I have or suspect a problem.

We'll run it here just like you would on a production system, but this time against your test system. You can find the tool on most Windows operating systems in the Start menu under Administrative Tools in the item called Performance.

Once you start that tool, you have a few choices, as I've documented before. I won't cover the specifics here, but on your test system you should set up a grid and change the maximum vertical scale to 10. On a production system, you can leave it at the default.

With that done, remove any active counters and add the following:

With those counters in place, take a moment and watch them. If you're working on a test instance with only yourself as the connection, the only counter you should see move is Lock Requests/sec. Even without any activity, the Query windows can generate traffic on your system.

Figure 284

The way you use this tool is to watch for excessive movement, especially during the times when users report slowdowns. If you can map locks or especially deadlocks to the times when you hear about the slowdowns, you can circle in on the locks that are causing the problem.

SQL Server 2005 includes a really neat feature. If you capture the Windows System Monitor information in a log, you can correlate it with the Profiler information you gather. Using these tools together you can find the exact query or queries that caused the lock action.

That being said, unless you tie something to the profiler, you can't really find the particular query that is causing the issue. It's primarily a tactic to determine that locks are the problem.

Stored Procedures

In both SQL Server 2000 and 2005, you can use two stored procedures to show locking on the system. With 2005, you get more information and more views, so I'll point those out along the way.

Let's assume your users are complaining about excessive slowdowns, and you've placed the counters I described on the system, and comparing it to your baseline counters, the locks are high, and even worse, you see a deadlock or two.

In both SQL Server 2000 and 2005 you can use two stored procedures to find out locking information, and any blocks or users that are causing them. The first is sp_lock, and the second is sp_who. Microsoft also includes sp_who2, a stored procedure that shows who is blocking whom, although this stored procedure isn't documented. In your third query window, paste these commands:

/* Third Query */
-- SQL2K, 2K5
EXEC sp_lock;
GO
EXEC sp_who2;
GO

You can run them now if you wish, but we'll run them again when we generate some activity. In the previous overview I wrote on locks, I explained the modes, lock types and other columns that these stored procedures returned. We'll see more of this information in a moment.

System Views

In SQL Server 2005, you can use Dynamic Management Views (DMVs) that return real-time information about the locking activity on your system. The primary DMV for locks is sys.dm_tran_locks. By combining this view with the sysprocesses system view, you can find out which processes are causing the locking activity. If you're using SQL Server 2005, paste this into the third query window as well:

SELECT resource_type
, resource_database_id
, resource_associated_entity_id
, resource_description
, request_mode
, request_session_id
, loginame
FROM sys.dm_tran_locks 
     INNER JOIN sysprocesses ON request_session_id = spid
ORDER BY resource_associated_entity_id ASC;
GO

SQL Server Profiler

I've explained how to use the SQL Profiler tool in another tutorial, so I won't cover that information here. The trace filters you should set for lock activity using SQL Profiler are all those that start with the word "Lock".

The advantage to SQL Profiler is that you can also include specific databases, and even include the text from the connections. That way you can pair up the actual text from the commands the user's connections are running to the locks or deadlocks they create.

SQL Trace

I've also explained how to use SQL Trace here. In effect this is just a text-based version of SQL Profiler, so you can use the same filters and objects to track locking as you do with Profiler. The advantage here is that you could create a SQL Server Alert which can fire on the deadlocking event, and trigger the SQL Trace to start. You could also schedule the SQL Trace with a SQL Server Job.

Locking and Deadlocking Example

With all of this information, let's go ahead and run a full example that will cause locks, blocks and a deadlock. You'll need to have the Windows System Monitor running, and either the Profiler or SQL Trace set up with the Locks objects.

You'll also want three query windows open, as I described earlier. In the third window you should have the stored procedures and (if you have SQL Server 2005) the query on the DMV for locking. We're going to need to fire off the first window, then quick like a bunny fire off the second, and then the third. After about a minute, you'll observe in all of your tools the effects of the locks and a deadlock.

In the first window, paste this query:

USE LockTest;
GO
CREATE TABLE TestTableA (Collumn1 int)
CREATE TABLE TestTableB (Collumn1 int)
INSERT TestTableA 
VALUES (1);
GO
INSERT TestTableB 
VALUES (2);
GO
-- First query
BEGIN TRANSACTION
UPDATE TestTableA 
SET Collumn1 = 10 WHERE Collumn1 = 1
WAITFOR DELAY ’00:00:50’
UPDATE TestTableB SET Collumn1 = 20 WHERE Collumn1 = 2
-- Uncomment the next lines for cleanup...
-- COMMIT
-- USE master;
-- GO
-- DROP DATABASE LockTest
-- GO
We're going to come back to that last bit after we get the deadlocking error. In the second query window, paste the following:
/* Second Query - causes the deadlock 
  by trying to update the column based 
  on the outcome of the first query */
WAITFOR DELAY ’00:00:05’
USE LockTest;
GO
BEGIN TRANSACTION
UPDATE TestTableB 
SET Collumn1 = 20 WHERE Collumn1 = 2
WAITFOR DELAY ’00:00:50’
UPDATE TestTableA 
SET Collumn1 = 10 WHERE Collumn1 = 1
COMMIT

Now, before we run these three queries, let's take a look at what we are doing. We've already created the test database, and entered some data in a table. In the first query, we're updating the table, changing all values by ten times. But in the second query (which represents a separate connection) we're using the new values to do the same thing – in essence we've made one query depend on the outcomes of another. That will cause a deadlock, and you'll see that activity reflected in the tools you're using to monitor the system.

Now press the F5 key on the first query, then the second query window, then the third. Switch between the monitoring tools and the query windows and eventually you'll get a deadlock error on the second query window.

Once you've observed the behavior, close the second query window and then uncomment and run the last part of the first query (just those last few lines). That will close out the query and kill the test database. You can repeat the experiment as many times as you like, trying out new monitoring methods each time.

Fixing the Locking Issues

So now you've identified a locking issue and you need to fix it. It isn't as simple as just killing the connection that is causing the lock, since it will come right back. Fixing the locking, blocking and deadlocking issues is often a matter of redesign. You'll have to examine either the schema of your database or more often the way the application is designed.

One of the most efficient ways to deal with locking is to ensure that the design of your system doesn't have to deal with excessive locks. The best way to do that is to normalize your tables, using more atomic structures that allow the query to get in and get out faster.

Another method is to code with the locks in mind. If the design requires less normalization, you'll have to evaluate the programs to ensure that they have the right isolation levels – which I'll cover in another tutorial,

InformIT Tutorials and Sample Chapters – Monitoring Locking and Deadlocking

Kevin Kline explains how you can write code to minimize locking here.

Online Resources – Monitoring Locking and Deadlocking

The Microsoft SQL Server reference for Locking is here.