Use sp_lock to diagnose SQL server performance problems

For details about the sp_lock command, see

Http://msdn.microsoft.com/zh-cn/library/ms187749.aspx

 

 

There is a common misunderstanding among IT experts that locking is a bad thing. You must do everything possible to ensure that the database is locked so that the process cannot run normally. To ensure a consistent database environment, the database engine must use a mechanism to obtain the exclusive permission for resources when modifying resources.

Locking is also used in SQL Server. To achieve this consistency, the database engine ensures that only one thread can access the same resource at a time. If you do not need to lock the database, data modification may occur for each process at the same time, which may cause the database to be in an inconsistent state. In this way, locking becomes a good thing. However, you should plan your application in a specific way to minimize the number of locks involved. In this article, I will discuss a stored procedure that allows you to analyze the database locking problem.

Find out what is locked

Slow system response means you should do some research. Your search should start with determining the number and frequency of system locks. If your system environment is highly transactional, it will be very common for applications to compete for resources and cause locking. The key to solving these problems lies in the ability to determine the locked resources and the process of competing for resources.

Sp_lock

Sp_lock: This system stored procedure is packaged with SQL Server 2000. It gives you an in-depth understanding of the locks in your system. This program will return a large amount of lock-related information from syslockinfo in the primary database, and the primary database is a system workbench that includes all the information that allows, converts, and waits for the lock request.

Let's take a look at what information it will provide for us after running the sp_lock program:

EXECUTE sp_lock

In my system, this is the content returned by the stored procedure. The information returned by sp_lock is not clear at a glance. To obtain useful data, you need to perform some searches. However, you can also copy the text of the stored procedure and create a new one to get a better explanation of the system process. (In this article, we will focus on the data returned by sp_lock .)

From the above results, we can see the spid, dbid, objid, indid, type, resource, mode, and status fields. Spid is the process identification number used to identify the connection to the SQL server. To discover which users are connected to the spid, execute the storage process sp_who and transmit the spid as a parameter to the program. Dbid is a locked database. You can find it in the sysdatabases table of the primary database. The objid field is used to display the object in which the lock occurs in the database. To view this object, you can query the specified objid in the sysobjects table of the primary database.

A single record generated on the screen above does not necessarily show what is happening in your work environment. When running this program, you want to find 500 to 1000 or more results. Each execution of sp_lock may have different results, because a new lock occurs, and some old locks have been removed. If you find that a large number of results returned by sp_lock have the same spid, it is very likely that the process is performing large-scale processing, and these locks may start to block new transactions.

When you find that a spid gets a lot of database locks, this will help you determine what stored procedures or statements are running. To achieve this, run the following DBCC command:

Dbcc inputbuffer (spid)

This DBCC command returns information about the statements that are running in the EventInfo field.

A reliable start point

Slow system running may indicate a large number of locks on your table. There are many reasons for these locks. For example, if a user is running a long query in your system, a process occupies a large amount of resources or two key processes compete for the same resource, which often causes deadlocks.

What should you do once you find that the process is slowing down your system? In most cases, you can only monitor the system. It is not wise to end this process because it involves many system locks, unless you are absolutely sure that there will be no other negative impact. Otherwise, you should find a way to automatically analyze the lock status. Another solution is to come up with a way to send a notification when the number of system locks reaches the limit within a day.

The more information you collect about your system, the more advantages you have in solving the problem.

Tim ChapmanIt's from a bank in Louver, Ky.SQL ServerThe database administrator has exceeded7Years of Experience in the industry.