Diagnosing SQL Server performance issues using sp_lock

http://articles.techrepublic.com.com/5100-10878_11-6088055.html

A common misconception among IT pros is that "locks are bad," and you must do everything possible to ensure database locking doesn't prevent processes from running. In order to ensure a consistent database environment, the database engine must use a mechanism to acquire exclusive use of a resource when that resource is being modified.

SQL Server uses locks, which are objects that the database engine uses to ensure that only one thread can access a resource at a time, to achieve this consistency. Without the use of locks, concurrent data modifications would be possible by separate processes, which could potentially leave the database in an inconsistent state. Locks are a good thing; however, you should plan your applications in such a way to minimize the number of database locks involved. In this article, I will discuss a stored procedure that enables you to diagnose your database locking issues.

Finding out what's getting locked

A sluggish running system means that it is time for you to do some investigation. A great place to start your search is to determine the number and frequency of locks occurring on your system. If your environment is highly transactional, it is very common for separate applications to contend for resources, which will cause locks. The key to resolving these issues is having the ability to determine the resources that are being locked and the processes that are contending for resources.

sp_lock

The sp_lock system stored procedure is packaged with SQL Server 2000 and will give you insight into the locks that are happening on your system. This procedure returns much of its information from the syslockinfo in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests.

Let's take a look at executing the sp_lock procedure to see what information it provides:

EXECUTE sp_lock

On my system, here's what the procedure returns . The information returned from sp_lock isn't self explanatory and will require some investigation to get to the meaningful data. However, you have the luxury to copy the text of this stored procedure and create a new one that will give you a better explanation as to what is going on in the system. (In this article, we will focus only on the data returned by sp_lock.)

 

 

Looking at the results above, we see the fields' spid, dbid, objid, indid, type, resource, mode, and status. The spid is the process identification number, which identifies your connection to SQL Server. To find out which user is associated with that spid, execute the stored procedure sp_who and pass the spid as a parameter to the procedure. The dbid is the database the lock is occurring in; you can find it in the sysdatabases table in the master database. The objid field indicates what object the lock is occurring on in the database. To view this object, you can query the sysobjects table in the master database for that specific objid.

The single record produced in the screenshot above will not necessarily be indicative of what is transpiring in your production environment. When you run this procedure, you want to look for result sets of 500 to 1000 or more. You will probably get a different result set each time you execute sp_lock because of the new locks that have been acquired and the older locks that have been released. If you find that a large number of records returned by sp_lock have the same spid, it is likely the process is inside of a large transaction, and these locks may begin to block other transactions from occurring.

When you notice a spid that has acquired a large number of database locks, it will be helpful to determine what stored procedure or statement is being run. To do this, run the following DBCC command:

DBCC INPUTBUFFER(spid)

This DBCC command will return information regarding the statement running in the EventInfo field.

A solid starting point

A slow running system could be indicative of a high number of locks on your tables. These locks could be caused by processes such as a user running a really long query on your system; a process that is a resource hog; or two critical processes that compete for the same resources and frequently deadlock.

Once you find the process that you think is slowing down your system, what should you do next? In most cases, there is nothing to do but monitor the system. It's not a good idea to kill a process because it has a large number of locks in the system, unless you are absolutely certain that nothing else is adversely affected. Instead, you should think of ways to automate the analyzation of your locking situation. Another idea is to figure out a way to receive notification when the system locks reach a certain threshold during certain times of the day.

The more information you can gather about your system, the greater advantage you will have regarding its resolution.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com .