Write your own sp_lock system stored procedure in SQL Server 2005
Posted January 8th, 2008 by Andrew PopoffThe sp_lock system stored procedure is a great tool for checking the amount of locking that occurs on your database system. It returns the number and types of locks that are being held by current active SQL Server sessions. One of my previous articles describes how you can use sp_lock to diagnose problems on your SQL Server.
There are, however, a few drawbacks to sp_lock. The results it returns are not very straightforward to the novice DBA. It does a great job showing what session is causing the largest number of locks on the system, but it does not show you much accompanying detail of those objects or sessions. You could create custom scripts to look up that information, but those results tend to be complicated, and the detail is mediocre at best. There were other system tables that you could use to look up lock information, such as syslockinfo, but that detail isn’t all that great either. Another huge drawback is that sp_lock and syslockinfo are deprecated features, so they may not be included in future SQL Server releases. SQL Server 2005 includes new dynamic management views that contain a lot of locking detail and make it easy to correlate locking information to make it much more meaningful.
Tags: sp_lock |
Leave a Reply