Locking is a natural part of any OLTP application. However, if the design of the applications and transactions...
are not done correctly, you can run into severe blocking issues that can manifest themselves in severe performance and scalability issues by resulting in contention on resources. Controlling blocking in an application is a matter of the right application design, the correct transaction architecture, a correct set of parameter settings, and testing your application under heavy load with volume data to make sure that the application scales well. This excerpt from Informit discusses how to identify blocking issues.
If proper considerations are taken at design time, you can develop a very robust system that scales very well.
However, you will run into some blocking issues at the time of deployment of the application under heavy load and under heavy multi-user scenarios. Also, if you are inheriting an application in which good design considerations were not adhered to, or if you are a consultant and have been asked to find out and fix the application, you need to know how to detect the application/db locking issues and how to resolve them.
The next few paragraphs detail such scripts and give you links to some relevant Microsoft Knowledge Base articles.
You should use SQL Server Profiler and T-SQL scripts to detect and log the locking and blocking issues in your environment(s).
All the T-SQL scripts that help in troubleshooting the locks and blocks in SS2k are typically based on the following:
- System objects to get metadata information such as sysobjects, syscolumns, and so on
- DBCC INPUTBUFFER command
There is an sp_blocker_pss80 procedure published by the Microsoft PSS team, and there are many variants of the same that are used by DBAs around the world. Access it here.
SQL Server Profiler
A sample profiler template can be used as the starting point, with additional filters for your application needs. The same can also be used for deadlocks if you want to run it for an extended time and know that you will be able to trace the deadlock event in case it happens (deadlocks will be covered in the second article of this series).
Always run the SQL Server Profiler trace from a client machine rather than running it directly on the production server. Be aware that the profiler is just a GUI tool, and the trace is really a server side trace. It just gives you good visibility into the data in a GUI format. You can also use script server side traces for detecting issues. Once you are done with the trace, you can then save the output of the trace file into a SQL Server table and directly query the data from that table to diagnose the flow of events and issues. Alternatively, you can also directly query the trace files by using the fn_trace_gettable function (refer to Books Online for more information).
Microsoft Knowledge Base Articles
The following Microsoft KB articles can help you more in troubleshooting locks and blocks: 263889, 830232 (the PSSDiag Utility), 323630, 271509, 295108, 307655.
To access these articles, use this search page and type in the KB article number.
Read more about resolving blocking issues at Informit.