SQL Server Clinic: Stop locking and blocking

Locking ensures SQL Server properly processes transactions, but too much locking can bring transactions to a grinding halt. This SQL Server Clinic walks you through a typical locking scenario and the steps needed to address locking and blocking problems.

Locking is a necessary component of the SQL Server engine to properly ensure transactions are processed accurately

with high levels of concurrency. Unfortunately, those locks can quickly become unwieldy and reduce SQL Server from a high concurrent database to a single-user machine with the click of a button. Now that is contention.

So how would you go about identifying, resolving and preventing such locking issues? In this SQL Server Clinic, I'll walk you through a typical locking example and the steps necessary to address the problems.

The scenario: Locking and blocking problem

Say certain accounting functions are executed on an order-entry system; the accounting application obtains millions of locks in the user-defined database and TempDB. The locks begin to escalate from page to extent and, in some circumstances, the table locks. The accounting process stops all of the order-entry users dead in their tracks. They are unable to use the application until the accounting application has finished. Why is that? With this level (shared and exclusive) and volume of locking, the spid (system process identifier) begins to block the order-entry users' spids when they try to access the same data until the accounting process is completed. Typically the order-entry users' spids become victims of the accounting application spid and are killed so the users are unable to complete their processing.

The diagnosis: Understand the problem

Determining when a locking and blocking situation occurs can be a difficult or easy proposition depending on the severity of the blocking. The goal during diagnosis phase is to understand the following:

  • Which processes (spids) are blocking?
  • What code (stored procedures, functions, ad-hoc, etc.) corresponds to the spids?
  • Which tables does the code share?
  • Which business processes map back to the code?

Luckily, SQL Server 2005 has a number of tools to help diagnose the issue to answer these key questions.

What you do not want is users informing you of a suspected locking scenario. This is the least favorable means to learn about a situation because the organization has probably suffered from the issue before. If this does occur, Activity Monitor is the easiest way to capture the current state of spids and overall locking and blocking. This application can be launched from the Management Studio by navigating to the Management folder and double clicking on the Activity Monitor icon, which will bring up a new interface.


Activity Monitor

A second option that actually delivers much of the same information is to query (i.e. SELECT statement) sys.dm_tran_locks, one of the new Dynamic Management Views (DMVs), to obtain information about the current state of the transaction's locks. This is a paradigm shift from the SQL Server 2000 world where system stored procedure sp_lock is used most often to find out the state of the current locks. Along these same lines, system stored procedure sp_who2 can return information about the spids, as well as any blocking conflicts.


New Alert

NOTE: sp_lock is still available in SQL Server 2005 and continues to return the spid, dbid, ObjId, IndId, Type, Resource, Mode and Status for backward compatibility, but the sys.dm_tran_locks offers a great deal of functionality and should be the SQL Server 2005 solution.

A third option is to set up SQL Server 2005 Alerts to notify the DBAs when locking is occurring in near real time. Alternatively, a script could be executed to handle the situation based on your business requirements. The screen shot on the left shows a sample alert for deadlocking occurring on the entire server. To access the SQL Server Alerts in Management Studio navigate to 'SQL Server Agent' and the 'Alerts' folder. Right click to select the 'New Alert' option. Next complete the 'General', 'Response' and 'Options' pages then press the 'OK' button to save the Alert.

The fourth option to capture information about locking and blocking is with SQL Server Profiler. The difference between Profiler and the remainder of these options is that this tool can be configured to capture all of the locking related information as well as the other processes that are running on the server and store the data for further analysis. So if you witness a scenario where locking and blocking occurs every afternoon and no one knows why, then Profiler may be the best place to start researching the issue, although all of the approaches can be used at the same time under normal circumstances.


Trace Properties

For more information about setting up a SQL Server 2005 Profiler session, see this tip.

A final option is to enable trace flag 1222, which will capture all deadlock-related information in an XML format. Trace flags can be enabled by visiting How to: Start an Instance of SQL Server (SQL Server Configuration Manager) in SQL Server 2005 Books Online.

The quick resolution: Stop the problem fast

To quickly stop a locking and blocking situation, the command to use in a query session in Management Studio is KILL with the spid number (i.e. KILL 59). This command should immediately stop the processing performed by this spid. To immediately stop the blocking, you must kill the correct spid, which would be the lead blocker. Once this spid is killed continue to monitor the server for blocking to ensure another spid will not rear its ugly head and begin blocking.

The long-term resolution: Prevent the problem from reoccurring

Using the KILL command is far from a permanent solution. Take the bull by the horns and address the issue rather than suffer through long-term, nagging blocking. Here are steps to resolve the problem in the long term:

  • Since you have already captured data with Profiler, run this session through the Database Engine Tuning Advisor to determine if your system would benefit from changes related to the indexes or partitions: If you experience table scans, adding indexes may be key; if excessive locking occurs, removing indexes could help.
  • Based on the spid analysis, revisit the database design to consider normalizing or denormalizing tables to meet your processing needs.
  • If batch processes are prevalent, as is the case with the order-entry system and accounting reconciliation, avoid using cursors, which cause processing at a single-row level rather than in row sets as SQL Server was designed to perform. Schedule processing during off hours and/or redesign business processes.
  • If reporting contributes to blocking, consider separating reporting from OLTP systems, aggregate the reporting data during non-business hours and provide users with the aggregate data and research snapshot isolation, a new feature in SQL Server 2005, where readers do not block writers. Essentially a version of the row is saved into TempDB for the reader to permit the writer to continue in an uninterrupted manner.

Conclusion

So is locking a bad thing after all? No. Is blocking normal at some level? Yes. Can applications be developed or redeveloped to correct these issues? Yes. Although locking and blocking is normal in some circumstances, it can easily become detrimental to long-term high performance. If you do not want it to rear its nasty head, an ounce of prevention can yield high dividends over the life of the application.

ABOUT THE AUTHOR

Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.


More information from SearchSQLServer.com

This was first published in January 2006

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close